从常规表中将索引传递给临时表？ [英] Pass index to temporary table from regular table?

问题描述

我正在使用如下查询创建临时表：

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

CREATE TEMPORARY TABLE temp_table
SELECT * FROM regular_table
WHERE 1

但是regular_table在某些字段上有FULLTEXT索引。我尝试在新的临时表上进行FULLTEXT搜索，并且收到错误告诉我找不到与列列表匹配的FULLTEXT索引。因此，索引不会复制到新表。有没有办法强迫这个？

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?

谢谢。

推荐答案

您可以使用 CREATE TEMPORARY TABLE temp_table LIKE regular_table ，但这将创建所有索引，因此当您执行 INSERT INTO时temp_table SELECT * FROM regular_table ，将重建索引 - 这可能很长。

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

再次，你将不得不等待索引构建，除非它将在一个块中发生，使用最后一个ALTER语句。

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

这篇关于从常规表中将索引传递给临时表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！