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
;