MySQL Ignoring Small Words in Full-Text Search Using MATCH AGAINST
I’ll start by jumping right in and providing an example of a scenario where search terms of a short length (less than four characters to be precise) might not be included in a MySQL query when using Full-Text searching and MATCH() AGAINST().
Let’s imagine we have the following simple table with just one column and two entries:
CREATE TABLE `names` ( `name` TEXT NOT NULL, FULLTEXT(`name`), /* apply full-text index*/ ); INSERT INTO `names` (`name`) VALUES ('Mr John Smith'), ('Dr Simon Smith')
Ok, now let’s try and do a search for the phrase ‘Dr Smith’ on this table using MATCH() AGAINST() and see what we get:
SELECT * FROM `names` WHERE MATCH(`name`) AGAINST('+Dr +Smith' IN BOOLEAN MODE) // Returns 2 rows: // Mr John Smith // Dr Simon Smith
I don’t know about you, but according to my calculations ‘Mr John Smith’ shouldn’t be coming back in the results.
So what’s going wrong?
I remember when I first ran into this problem it took me ages to a) understand what exactly was going wrong and b) find out how to fix it. I looked at other alternatives such as using a LIKE condition, but these just weren’t as efficient.
That was until I found this page and its mention of the ‘ft_min_word_len‘ variable in the MySQL configuration file, my.cnf.
In my example, a quick addition of this variable to my.cnf resolved the problem like so:
One restart of MySQL and a rebuild of the index later, and the correct results were being returned.