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

MySQL Full Text Searching — is it possible to speed up query with additional where clauses or joins?

I’ve noticed that full text searches in MySQL do not seem to speed up when reducing the search pool with additional where clauses or joins.

For example:

SELECT * FROM `blog_posts`
WHERE `id` = 1
AND MATCH (post_body) AGAINST ('+mango +smoothie' IN BOOLEAN MODE);

takes just as long as

SELECT * FROM `blog_posts`
WHERE MATCH (post_body) AGAINST ('+mango +smoothie' IN BOOLEAN MODE);

So it seems as if the MATCH clause always works against the entire database, even if you pre-filter it down with restrictive where clauses. Is that as designed and is there any way around this? I have cases where I want to run my boolean search clause against a fairly small subset, and the query is taking very long.

Tags:

2

Answers


  1. 0

    Yes, it is as designed.

    MySQL will typically use one index per table reference. If it uses the fulltext index, it does not use another index.

    So it doesn’t matter if you have additional conditions or joins or indexes to support those conditions or joins. Either way, it will be forced to examine all the rows that match the fulltext search.

    Login or Signup to reply.
  2. 0

    Think of it this way. If there are multiple clauses in WHERE, the FT test will be performed first — on the assumption that it will do the best filtering.

    Furthermore, FT is not designed to work on anything smaller than the entire table, so even if you could feed in a subset of rows, it would probably run slower.

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

Back To Top
Search