Search code examples
mysqlfull-text-searchfull-text-indexing

Why mysql fulltext search not run as expected?

I have a table with 700, 000 rows. One column called 'data' is text type. I add fulltext index on this column to improve my query speed.

Here are two query, the second not return as expected.

You can see that the first query return one result with the keywords I specified. It took 2 seconds

I thought the second query should run faster since I give more filter condition. But it tooks about one minute.

Giving more conditions should narrow down the data set to search, why it slower?

MYSQL version is 8.0.16 Engine is InnoDB. Sorry about the Mosaic

enter image description here

Solution

  • Giving more conditions should narrow down the data set to search, why it slower?

    FULLTEXT will search for each required string, getting a list of row numbers (or something equivalent).

    For multiple required strings, it will get multiple lists and "AND" them together.

    Furthermore, when there are two strings ("26228" and "31500733" in quotes together, their adjacency needs to be verified. This may be the slow part.

    Consider this instead

    MATCH AGAINST(+uf8... +26228 +31500733 IN BOOLEAN MODE)

    That won't test the adjacency, but that might not matter to the end results. (Note also that I skipped the too-short "i".)