How do FULLTEXT INDEXES on multiple columns work?

Question

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

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

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

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

Solution 1

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 [...]) ...

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.

Solution 2

Only one index, of type FULLTEXT, will be created. That index will "span" around multiple columns if necessary. However, the indexes themselves are not indexing the columns as much as their words. From the documentation:

InnoDB FULLTEXT indexes have an inverted index design. Inverted indexes store a list of words, and for each word, a list of documents that the word appears in. [...]

If you create one FULLTEXT grouping three columns, or three FULLTEXT grouping only one, these columns will in effect consist of entries in the FULLTEXT meta-data tables.

When incoming documents are tokenized, the individual words (also referred to as “tokens”) are inserted into the index tables along with position information and the associated Document ID (DOC_ID).

Basically, it seems multi-column FULLTEXT vs multiple single-column FULLTEXT indexes work the same, as the storage of the words is abstracted from the original separation of the columns.

Solution 3

In MySQL, you need to create a separate index on each set of columns you plan to use (if you are using the natural language search. This may differ for the boolean search).

From the manual:

For natural-language full-text searches, it is a requirement that the columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table. [...] If you wanted to search the title or body separately, you would need to create separate FULLTEXT indexes for each column.

Comments

[Martin Burch]

This is the Microsoft SQL Server syntax, and a link to Microsoft documentation. In MySQL, I think you need to name the index.

[Andrew]

THE question: is order of columns important? You must have the same columns, OK, but what about order? Can I mix those columns in MATCH while keeping the same multi-column index?

[John Zenith]

No, the order is not important. The columns in the fulltext index can be placed in any sort of order, but all the columns as specified when creating the fulltext index must be referenced in the match() query.

[John Zenith]

You could create separate fulltext indexes but then you may want to use OR to create a cluster for the results, and the OR keyword is not a good query optimizer.

[user3789115]

Sequence matters See documentation, dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html