Mysql – FULLTEXT index ignored in BOOLEAN MODE with ‘number of words’ conditional

full-text-searchMySQL

fulltext match is ignoring its index when I add a number of words conditional for its boolean mode. The selects are as follows:

explain select * from seeds  WHERE  MATCH(text) AGAINST ("mount cameroon" IN BOOLEAN MODE);

outputs 

+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | seeds | fulltext | text          | text | 0       |      |    1 | Using where |
+----+-------------+-------+----------+---------------+------+---------+------+------+-------------+

the same query with a number of words conditional 

explain select * from seeds  WHERE  MATCH(text) AGAINST ("mount cameroon" IN BOOLEAN MODE) = 4;

outputs

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | seeds | ALL  | NULL          | NULL | NULL    | NULL | 9607 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

surely this cant be correct behaviour?

Best Answer

I have very bad news for you.

Unfortunately, that is the correct behavior.

The MySQL Query Optimizer tends to get sidetracked very easily when using a FULLTEXT index.

I also have very good news for you.

I wrote about this in StackOverflow on how to get around it.

You may have to nest your original query in a subquery and return the MATCH function as a column. Then, evaluate the MATCH column outside of the subquery.

Instead of your query

select * from seeds  WHERE  MATCH(text) AGAINST ("mount cameroon" IN BOOLEAN MODE) = 4;

you must refactor it into something like this:

SELECT B.* FROM
(
    SELECT id,MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE) score
    FROM seeds
    WHERE MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE)
) A
INNER JOIN seeds B USING (id)
WHERE A.score = 4;

Give it a Try !!!

Related Question