Slow mysql fulltext query I have a little problem with the performance of a mysql-query which uses a fulltext index.

The following query

SELECT Mention.id
FROM mentions AS Mention  
WHERE (MATCH (`Mention`.`title_text`, `Mention`.`content_text`, `Mention`.`author_text`) 
AGAINST ('"hannover 96"' IN BOOLEAN MODE))


takes about 3 seconds.

If I change the fulltext-condition to 

SELECT Mention.id
FROM mentions AS Mention  
WHERE (MATCH (`Mention`.`title_text`, `Mention`.`content_text`, `Mention`.`author_text`) 
AGAINST ('+hannover +96' IN BOOLEAN MODE))


the query takes just about 0.001 seconds.

So is there a possibility to get a better performance of the first query? It’s important to search for the complete string “hannover 96” and not for records which contain hannover and 96.

Thanks for some tips!

Best regards,

Timo
I can explain why the first query is slow, but not why the second one is fast. :)

My theory is that ft_min_word_len is longer than "2" on your system (default is 4) so the "word" '96' is not actually making it into the full-text index... so you're doing a full table scan or at least you're having to find all of the rows containing 'hannover' in the index and then discarding the rows not matching the final criteria.  Unfortunately, EXPLAIN SELECT isn't as valuable as usual when it comes to queries involving a fulltext index.  It just tells you whether that index is going to be attempted, apparently.

Take a look at these:  

https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_ft_min_word_len

If you aren't, already, then you may want to use SELECT SQL_NO_CACHE ... when benchmarking queries... or you may be getting a response from the query cache that is deceptively speedy.