多个列上的FULLTEXT索引如何工作？ [英] How do FULLTEXT INDEXES on multiple columns work?

问题描述

在3列中添加FULLTEXT INDEX时，是否在3列中添加1个索引，还是添加3个不同的索引？

When adding a FULLTEXT INDEX on 3 columns, does that add 1 single index on 3 columns, or does it add 3 separate indexes?

我现在使用FULLTEXT像这样：

I ask this, because at the moment I'm using FULLTEXT like this:

ALTER TABLE myTable ADD FULLTEXT all3colsIndex (col1,col2,col3);
SELECT * FROM myTable WHERE MATCH (col1, col2, col3) AGAINST ('word');

我刚在搜索界面中添加了一个用户选项，用户可以从中删除其中一个列搜索。所以我可以这样做而不丢失索引，其中我只使用3列中的2：

I have just added a user option to my search interface where the user can remove one of the columns from the search. So am I able to do this without losing the index, where I'm only using 2 of the 3 columns:

ALTER TABLE myTable ADD FULLTEXT all3colsIndex (col1,col2,col3);
If (UserOptionRemoveCol == "selected") {
    SELECT * FROM myTable WHERE MATCH (col1, col2) AGAINST ('word');
} else {
    SELECT * FROM myTable WHERE MATCH (col1, col2, col3) AGAINST ('word');
}

或者我必须在两列上创建一个新的FULLTEXT索引以及三个？

Or would I have to create a new FULLTEXT index on the two columns, as well as the three?

ALTER TABLE myTable ADD FULLTEXT all3colsIndex (col1,col2,col3);
ALTER TABLE myTable ADD FULLTEXT 2colsIndex (col1,col2);

推荐答案

查看 CREATE FULLTEXT INDEX ，表示您可以指定多个列重复 column_name ：

Glancing over the manual for CREATE FULLTEXT INDEX, it indicates that you are able to specify multiple columns by repeating the column_name as such:

CREATE FULLTEXT INDEX ON table_name (column_name1 [...], column_name2 [...]) ...

，我会假设它在3列中创建单个索引。此外，我假设它工作在从左到右的规则关于复合索引（我会验证这通过检查以下语句的执行计划）。因此，必须以该顺序选择（col1，col2，col3）上的复合索引（ SELECT col1，col2 ... ）。如果你打电话 col2 ，它不会使用索引。

Given this information, I would assume it creates a single index across 3 columns. Further, I'm assuming that it works under the left-to-right rule with regards to composite indexes (I would verify this by checking the execution plan for the following statements). Therefore, a composite index on (col1, col2, col3) would have to be selected in that order for it to be used (SELECT col1, col2 ...). If you were to call col2 it would not use the index.

这篇关于多个列上的FULLTEXT索引如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！