Slow mysql fulltext query

Question

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

Answer 1

The WHERE clauses of your queries are asking for completely different things

• QUERY #1: exact string hannover 96 within the three columns mentioned in MATCH clause.
• QUERY #2: the two strings hannover and 96 within the three columns mentioned in MATCH clause.

Fulltext indexes index tokens very well. Consequently, exact strings that contains multiple tokens should take longer to process.

The only way to find hannover 96 faster is to use a subquery.

SELECT B.* FROM
(SELECT Mention.id FROM mentions AS Mention  
WHERE (MATCH (`Mention`.`title_text`,`Mention`.`content_text`,`Mention`.`author_text`) 
AGAINST ('+hannover +96' IN BOOLEAN MODE))) A
INNER JOIN Mention B USING (id)
WHERE LOCATE('hannover 96',CONCAT(title_text,content_text,author_text)) > 0;

or

SELECT B.* FROM
(SELECT Mention.id FROM mentions AS Mention  
WHERE (MATCH (`Mention`.`title_text`,`Mention`.`content_text`,`Mention`.`author_text`) 
AGAINST ('+hannover +96' IN BOOLEAN MODE))) A
INNER JOIN Mention B USING (id)
WHERE LOCATE('hannover 96',  title_text) > 0
OR    LOCATE('hannover 96',content_text) > 0
OR    LOCATE('hannover 96', author_text) > 0;

Give it a Try !!!

Answer 2

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.