The performance of mysql fulltext depend on order of keyword I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result:


FAST (1s)

SELECT *
FROM   table
WHERE  (MATCH (column_data) AGAINST('+english* +php*' IN BOOLEAN MODE)

SLOW (10s)

SELECT *
FROM   table
WHERE  MATCH (column_data) AGAINST('+php* +english*' IN BOOLEAN MODE)

FAST (1s)

SELECT *
FROM   table
WHERE  MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE)
AND    (MATCH (column_data)) AGAINST('+english*' IN BOOLEAN MODE))

SLOW (10s)

SELECT *
FROM   table
WHERE  (MATCH (column_data) AGAINST('+english*' IN BOOLEAN MODE))
AND    (MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE))



How can I optimize it?
https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE `table`;


Please run that, time your variations again, and report back.

If it fails to help, report a bug at bugs.mysql.com .