MySQL Ignoring Small Words in Full-Text Search Using MATCH AGAINST

September 23rd, 2012 - Posted by Steve Marks to Web Development.

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:

   `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:

FROM `names` 

// 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?

The Solution

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.

This entry was posted on Sunday, September 23rd, 2012 at 2:29 pm by +Steve Marks and is filed under Web Development. You can follow any responses to this entry through the RSS 2.0 feed.

Fear not, we won't publish this

Comments (1)
  1. Vrt says:

    Hey! Thank you for this solution it was really helpful.