6

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?

  • Platform and version? – Nick Chammas Oct 24 '11 at 12:10
  • This question gets a +1 because this symptom caused by FULLTEXT indexes is rarely discussed. +1 for bringing this topic up. – RolandoMySQLDBA Oct 25 '11 at 15:38
3

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

  • Does this apply to both InnoDB and MyISAM? – Rick James Aug 30 '18 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.