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
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 ;