Question 21500 in Mysql Question posted in
The official MySQL documentation can be found here.

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search