Mysql – Slow thesql fulltext query

full-text-searchMySQLoptimizationperformancequery-performance

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

Best Answer

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;

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

Related Solutions

Mysql – No results for match…against in boolean mode search with fulltext index (MySQL)

I ended up asking a DBA, and he said that the comma is recognized as a word separator in MySQL. So, that meant that it was looking for "asp" and "net", both of which are under the 4 character minimum for words to be included in a fulltext index. I added the configuration directive ft_min_word_len=3 to my.cnf, restarted mysqld, and rebuilt the fulltext index on the table. Using MATCH(column-name) AGAINST ('+asp +net' IN BOOLEAN MODE); now locates the proper records.

Thanks everyone for your help!

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Execution plan XML would be useful here but as a shot in the dark:

Daft as it may sound, try pushing either the [id] or date predicate into the CONTAINSTABLE query. See SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned - Consider embedding filter conditions as keywords in the indexed text.
I don't remember where but I recall reading an article or blog post some time ago that flagged big OR full-text queries as problematic. IIRC the suggested hack/workaround was to issue a UNION query instead.

For your example, the UNION for 2) would be along the lines of:

INSERT
    @processed_rules
    (
    rule_id
    , story_id
    )
SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term one"')

UNION

SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term two"')

UNION

  SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term three"')

Best Answer

Related Solutions

Mysql – No results for match…against in boolean mode search with fulltext index (MySQL)

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Related Question