FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

Question

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?

• Nick Chammas: Platform and version?
• RolandoMySQLDBA: This question gets a +1 because this symptom caused by FULLTEXT indexes is rarely discussed. +1 for bringing this topic up.

Answer 1

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 !!!

• Rick James: Does this apply to both InnoDB and MyISAM?