Mysql – Why doesn’t a MySQL fulltext search “IN BOOLEAN MODE” fail when referencing a missing column

full-text-searchmyisamMySQLmysql-5.7

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
;

Best Answer

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

Related Question