Is index are available in temperary table

cy163 · #1 (**permalink**) 10-16-10, 11:16

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);

it-iss.com · #2 (**permalink**) 10-16-10, 16:05

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)

cy163 · #3 (**permalink**) 10-17-10, 06:54

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);

it-iss.com · #4 (**permalink**) 10-18-10, 07:54

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech