Re: COUNT query is slow using Multiple MATCH AGAINST with OR operator

[Rick James]

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;