CODEWITHSUNDEEP
mysqlperformancefull-text-searchsql-likefull-text-indexing
Caleb Pitman
Caleb Pitman

Reputation: 1165

MySQL using OR with MATCH AGAINST slows query drastically

Columns are indexed and fulltext. Why does the "OR" add 5-8 seconds? 

SELECT * FROM exampleTable WHERE MATCH(someColumn) AGAINST('+testing123*' IN BOOLEAN MODE) 
// returns in .05 seconds
SELECT * FROM exampleTable WHERE someColumn LIKE 'testing123%' 
// returns in .003 seconds
SELECT * FROM exampleTable WHERE (MATCH(someColumn) AGAINST('+testing123*' IN BOOLEAN MODE) OR MATCH(someColumn) AGAINST('+testing123*' IN BOOLEAN MODE)) 
// returns in 5-8 seconds; yes, they are same MATCH statement...
SELECT * FROM exampleTable WHERE (MATCH(someColumn) AGAINST('+testing123*' IN BOOLEAN MODE)OR someOtherIndexedColumn LIKE 'testing123%' 
// returns in 5-8 seconds; ***** different columns this time*******

I find it odd that MySQL is so slow when it's the same statement. It's like causing the statement "SELECT * FROM tableName WHERE 1 OR 1 " to be 5 seconds. Whether the columns are the same or different makes no difference in speed.

Upvotes: 1

Views: 557

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53870

Though MySQL 5.0+ now supports Index Merge, there currently is this limitation:

Index Merge is not applicable to full-text indexes. We plan to extend it to cover these in a future MySQL release.

Typically, you can rewrite these into UNION queries:

SELECT * FROM exampleTable
WHERE MATCH(someColumn) AGAINST('+testing123*' IN BOOLEAN MODE)
UNION
SELECT * FROM exampleTable
WHERE MATCH(someColumn) AGAINST('+testing123*' IN BOOLEAN MODE)

Upvotes: 4

Related Questions