mysqlindexingfull-text-searchquery-optimizationdatabase-indexes

MySQL SELECT on multiple FULLTEXT indexes. Extremely slow results

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

Solution

  • 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.