MySQL SELECT on multiple FULLTEXT indexes. Extremely slow results-CodePudding

10 million rows. MySQL server V. 5.7 Two indexes called "tagline" and "experience".

This statement takes < 1 second:

SELECT count(*) FROM pa
    WHERE MATCH(tagline) AGAINST('"developer"' IN BOOLEAN MODE);

This statement also takes < 1 second:

SELECT count(*) FROM pa
    WHERE MATCH(experience) AGAINST('"python"' IN BOOLEAN MODE);

This combined statement takes 30 seconds:

SELECT count(*) FROM pa
    WHERE MATCH(tagline) AGAINST('"developer"' IN BOOLEAN MODE)
      AND MATCH(experience) AGAINST('"python"' IN BOOLEAN MODE);

Similar problem outlined here. Essentially slight alterations to fulltext match make it useless: https://medium.com/hackernoon/dont-waste-your-time-with-mysql-full-text-search-61f644a54dfa

CodePudding user response：

Change the last one to

SELECT count(*) FROM pa
    WHERE MATCH(tagline, experience) AGAINST(' developer  python' IN BOOLEAN MODE)

and add

FULLTEXT(tagline, experience)

(I am assuming you are using Engine=InnoDB.)

Be aware that when using MATCH, it is performed first; anything else. In your case, one MATCH was performed, then it struggled to perform the other, there is way to run a second MATCH efficiently.

CodePudding user response：

Went with Sphinx. https://www.youtube.com/watch?v=OP0c26k_iQc

Fairly easy way of upgrading the capabilities of MySQL without committing to a new stack.