MySQL Forums
Forum List  »  Full-Text Search

Re: COUNT query is slow using Multiple MATCH AGAINST with OR operator
Posted by: Rick James
Date: January 09, 2011 03:30PM

OR is often not well optimized. The workaround is to remove OR, and rewrite using UNION:

SELECT ... -- one MATCH
UNION DISTINCT
SELECT ... -- the other MATCH

(Depending on your situation, UNION ALL may work and be slightly faster.)

Since you are just doing a count, this might be the actual formulation:
SELECT COUNT(*)
    FROM (
        SELECT id search_count FROM performance_search
            WHERE MATCH(AuthorNames) AGAINST ('david*' IN BOOLEAN MODE)
        UNION DISTINCT
        SELECT id FROM performance_search
            WHERE MATCH(JournalArticleTitle,JournalArticleAbstract) AGAINST ('safe*' IN BOOLEAN MODE)
         ) x;

I suspect this would run at the same speed:
SELECT COUNT(DISTINCT id)
    FROM (
        SELECT id search_count FROM performance_search
            WHERE MATCH(AuthorNames) AGAINST ('david*' IN BOOLEAN MODE)
        UNION ALL
        SELECT id FROM performance_search
            WHERE MATCH(JournalArticleTitle,JournalArticleAbstract) AGAINST ('safe*' IN BOOLEAN MODE)
         ) x;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: COUNT query is slow using Multiple MATCH AGAINST with OR operator
2672
January 09, 2011 03:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.