CODEWITHSUNDEEP
mysqlperformancefull-text-search
Maniac_1979
Maniac_1979

Reputation: 979

mysql fulltext exact phrase slow

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?

Upvotes: 3

Views: 2436

Answers (1)

Amit
Amit

Reputation: 1385

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

Upvotes: 4

Related Questions