mysql fulltext exact phrase slow

full-text-search, mysql, performance

Solution

I am not sure about the exact match query performance, but can you please try below query and see what is the performance?

SELECT COUNT(*) 
FROM   report 
WHERE  MATCH (reporttext) AGAINST ('porttitor vulputate' IN BOOLEAN MODE)   
  AND  reporttext LIKE '%porttitor vulputate%' LIMIT 0, 1000

Problem

I'm using InnoDB Full text searching on a table. I have created 400.000 dummy reports and created a fulltext. The speed when searching one word is excellent. ``` SELECT count(*) from report where MATCH (reporttext) AGAINST ('dolor' IN BOOLEAN MODE) LIMIT 0, 1000 1 row(s) returned 0.717 sec / 0.000 sec count(*) 199629 ``` When doing explain of the query : ``` id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE report fulltext FTReportText FTReportText 0 NULL 1 "Using where" ``` So far so good I would say, now when we run the following query: ``` SELECT count(*) from report where MATCH (reporttext) AGAINST ('"porttitor vulputate"' IN BOOLEAN MODE) LIMIT 0, 1000 1 row(s) returned 50.732 sec / 0.000 sec count(*) 22947 ``` Explain : ``` id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE report fulltext FTReportText FTReportText 0 NULL 1 "Using where" ``` As you can see for the 2nd query now the time is approx 50 seconds. Is exact phrase search that expensive? Am I missing something here?

Original source