Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: Is index available in temporary table

    Hi All,

    There is a table tbl
    Code:
    CREATE TABLE tbl (
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL,
    INDEX (name),
    INDEX (value)
    )
    I create a temporary table tmp_tbl by
    Code:
    CREATE TEMPORARY TABLE tmp_tbl(
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
    )
    and, populate the temporary table by
    Code:
    SELECT * 
      FROM  tbl
      WHERE name LIKE '%ABC%'
    INTO tmp_tbl
    I wonder if the indexes on 'name' and 'value' of the underlying table 'tbl' are still available on tmp_tble?

    Moreover, I wonder if it is possible to create new index on a temporary table using
    Code:
    create index IDX_test  on  tablename (fieldname);
    Last edited by cy163; 10-17-10 at 10:54.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    the SELECT INTO syntax is not correct in this case. This should have been written as follows:

    Code:
    INSERT INTO tmp_tbl(name, value) SELECT name, value FROM tbl WHERE name LIKE '%ABC%';
    When you copy data from one table to another it does not automatically copy across their indexes too. These need to be created. It is possible to create an index on a temporary table as follows:

    Code:
    mysql> create index idx_test on tmp_tbl (name);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    mysql> show index from tmp_tbl;
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | tmp_tbl |          1 | idx_test |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    1 row in set (0.03 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    Thanks for your reply.

    Further question on indexes on temporary table.
    if I include the indexes when creating a temporary table
    Code:
    CREATE TEMPORARY TABLE `rank_temp` (
    `id` BIGINT UNSIGNED NOT NULL,
    `rank1` INT NOT NULL ,
    `data1` DOUBLE NOT NULL ,
    `rank2` INT NOT NULL ,
    `data2` DOUBLE NOT NULL ,
    PRIMARY KEY ( `id` ) ,
    INDEX ( `data1` ) ,
    INDEX ( `data2` )
    ) ENGINE = MEMORY;
    then I populate it
    Code:
    INSERT INTO rank_temp (id, rank1, data1, rank2, data2)
      SELECT id, 0, data1, 0, data2
      FROM rankme;
    in this case, do I still need to use the following statements to explicitly create indexes
    Code:
    create index idx_test on rank_temp (data1);
    create index idx_test on rank_temp (data2);
    Last edited by cy163; 10-17-10 at 08:13.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Yes you do. The INSERT INTO statement is simply copying the data from the results of the SELECT statement. It is not copying the table or any of its constraints or indexes. These must be done explicitly. Remember also that the CREATE INDEX <index name> must be a unique name, in your example above the second one will fail.

    However, you should also consider the number of rows that will be stored in memory at any one time. This is particularly important as creating and using indexes on a small set of data will have an overhead which could end up giving you worse performance than just using a table scan. A table scan in memory will be very quick.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •