Pass index to temporary table from regular table?

Question

I am creating a temp table with a query like this:

CREATE TEMPORARY TABLE temp_table
SELECT * FROM regular_table
WHERE 1

But regular_table has FULLTEXT index on some of the fields. I try to do a FULLTEXT search on the new temporary table and I get an error telling me "Can't find FULLTEXT index matching the column list". So obviusly the index is not copying over to the new table. Is there a way to force this?

Thanks.

Answer 1

A temporary table is exactly the same as any other table except that it will be dropped at the end of the session. The only way to have the same indexes (from within the database) is to create them on the table as you would any other table.

Now there is a bit of a hack. You can copy the physical files on disk to a new name and have a clone of the table which includes indexes but I'm assuming you're doing this within an app so that might not be very practical.

Answer 2

You could use CREATE TEMPORARY TABLE temp_table LIKE regular_table, but that will create all the indexes, so when you do INSERT INTO temp_table SELECT * FROM regular_table, the indexes will be rebuilt - which could be lengthy.

Or, you can create the table and add the index afterwards:

CREATE TEMPORARY TABLE temp_table
ALTER TABLE temp_table ADD FULLTEXT INDEX (foo,bar,baz)
INSERT INTO temp_table SELECT * FROM regular_table

but the index will be, again, updated on every insert.

Probably the most efficient way would be to create the temp table, insert all, build index afterwards:

CREATE TEMPORARY TABLE temp_table
ALTER TABLE temp_table ADD FULLTEXT INDEX (foo,bar,baz)
ALTER TABLE temp_table DISABLE KEYS
INSERT INTO temp_table SELECT * FROM regular_table
ALTER TABLE temp_table ENABLE KEYS

Again, you will have to wait for the index to build, except it will happen in one chunk, with the last ALTER statement.