Reputation: 979
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
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