1

First, the background on how I discovered this issue. This happened when using MySQL 5.7 and the DB — as well as the related table — are using MyISAM.

I was debugging some code — and related MySQL queries — on a website I manage and found the bottleneck: It turns out a fulltext search was running a MATCH referencing a column that was not a part of the fulltext index for the table with 100,000+ rows. Hooray! I rebuilt the fulltext index adding the missing column, and suddenly a query that took 4.7 seconds to run ran in 0.0007 seconds!

But here is what baffles me: Why didn’t a MySQL fulltext search IN BOOLEAN MODE fail when referencing a missing column? Instead — in this case — it just ran slowly but gave correct results. Why didn’t it just fail? Is this a bug or a feature?

I mean, when I ran the same query in other fulltext modes such as these:

  • AGAINST ('Happy' IN NATURAL LANGUAGE MODE)
  • AGAINST ('Happy' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
  • AGAINST ('Happy' WITH QUERY EXPANSION)

I immediately got this MySQL error:

#1191 - Can't find FULLTEXT index matching the column list

To me, a failure like that would have been more useful than MySQL seemingly doing a full table search. Instead, the query was simply degraded to being accurate but extremely slow.

The initial fulltext index was built something like this; note the missing MOOD column:

ALTER TABLE `bigmoods`
  DROP INDEX `bigmoods_fulltext_idx`,
  ADD FULLTEXT `bigmoods_fulltext_idx`
  (
    `FIRSTNAME`,
    `LASTNAME`
  );

The query was something like this:

SELECT
  SQL_CALC_FOUND_ROWS
  FIRSTNAME,
  LASTNAME,
  MOOD,
  MATCH (
    `FIRSTNAME`,
    `LASTNAME`,
    `MOOD`
  )
  AGAINST ('Happy' IN BOOLEAN MODE)
  AS search_score
FROM
  bigmoods
WHERE
  MATCH (
    `FIRSTNAME`,
    `LASTNAME`,
    `MOOD`
  )
  AGAINST ('Happy' IN BOOLEAN MODE)
GROUP BY
  LASTNAME
ORDER BY
  LASTNAME ASC
LIMIT 0,100
;
0

1 Answer 1

2

This is documented behaviour:

InnoDB tables require a FULLTEXT index on all columns of the MATCH() expression to perform boolean queries. Boolean queries against a MyISAM search index can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

Why this exception exists (which is what you are asking in a comment) is speculation and something to be answered by the original developers, but there is something that you cannot do without this exception:

a boolean search that spans multiple tables.

For a natural search, you could compensate with or over separate matches (one for each table), for a boolean search, it would fall into the range of "much harder" to "impossible", which could explain the asymmetry.

If that was added as a nice feature to the (MyISAM) boolean search, and at least one user relied on it, you can make an argument for not removing it, as the only disadvantage of not forcing an index is that it could be slow if you make the mistake of forgetting the index (which can and will happen with normal indexes too, see 90% of SQL performance questions on Stack Overflow).

MySQL is really not in the business of protecting users from themselves, see e.g. only_full_group_by=0 (that can give you unintended results if you are not careful) or grant (that, before MySQL 8.0, could create a new user without a password if you are not careful).

3
  • Good answer. But if that is the case, why then does it fail when I use the same exact MyISAM table and switch to using NATURAL LANGUAGE MODE, NATURAL LANGUAGE MODE WITH QUERY EXPANSION and WITH QUERY EXPANSION? Maybe that is the bug? That it fails with other modes but only works with BOOLEAN mode? Sep 6, 2020 at 15:26
  • 1
    My speculation(!) was: a natural search in 2 tables could be match(t1.c1) against ('a lot of words') or match(t2.c2) against ('a lot of words') to get a similar effect as match(t1.c1, t2.c2) against ('a lot of words'). For boolean mode, that would be more complicated, e.g. match(t1.c1, t2.c2) against('+word1 -word2') could be e.g. (match(t1.c1) against ('+word1') or match(t2.c2) against ('+word1')) and not (match(t1.c1) against ('word2') or match(t2.c2) against ('word2')), and that's a simple 2 word case. So a feature (only needed for boolean) could have been to make this simpler.
    – Solarflare
    Sep 6, 2020 at 18:07
  • Gotta be honest: I am barely absorbing your examples. But man, speeding up a boolean search from 4.7 seconds to 0.0007 seconds was a relief. I only wish there was some way to make it clearer that the search was choked on a missing column in the index. Live and learn! Thanks for your help. Sep 6, 2020 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.