MYSQL FULLTEXT Index Performances

Question

I have Mysql table with multiple fields and I am using api to search one or multiple words in thoses labels :

myTable(id, label1, label2, label3, label4, label5)

So I created 1 FULLTEXT Index on all fields ( label1, label2, label3, label4, label5) Using request :

CREATE FULLTEXT INDEX search_index_mytable ON myTable(label1, label2, label3, label4, label5);

Example of use : I want to find words : word1, word2, word3

-- example 1
SELECT id 
FROM myTable 
WHERE MATCH(label1, label2, label3, label4, label5 )
      AGAINST ('word1* word2* word3*' IN BOOLEAN MODE)

-- example 2
SELECT id 
FROM myTable 
WHERE MATCH(label1, label2, label3, label4, label5 ) AGAINST ('word1*' IN BOOLEAN MODE)
   OR MATCH(label1, label2, label3, label4, label5 ) AGAINST ('word2*' IN BOOLEAN MODE)
   OR MATCH(label1, label2, label3, label4, label5 ) AGAINST ('word3*' IN BOOLEAN MODE)

My question is : which request example is fastest ? are they equal?

Thank you so much

Answer 1

Example 1 is faster; that is part of the beauty of FULLTEXT.

You may not need the * on the ends of the words. "Stemming" is done anyway.

You may need a + in front of each word. This depends on whether you want "or" or "and" logic.

Beware of "short" words and "stop words".

Be aware that the index you built will always test against all 5 columns; if you need to test against only one column, you need another FT index.