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.

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

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer
    
Thanks for your answer! The queries with the subqueries are really faster! Unfortunately it’s very difficult for me to build the query in this structure in my application. So is there maybe a chance to get a better performance of the fulltext-query with multiple tokens? –  Timo Oct 16 '12 at 4:32

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.

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.