Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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