MySQL: Change minimum word length for full text search

By default, the fulltext search of MySQL, is set, that words under a length of four letters will be excluded from the search. So, if you use a MySQL query like 

SELECT * FROM tab WHERE MATCH (col1, col2) AGAINST
('+searchterm +ABC' IN BOOLEAN MODE);

it will be searched for "searchterm" correctly, but the word "ABC" will be sorted out and has no influence on the search result.

This is problematic, for example, in product directories, where there are many words of 3 letters or also with searching for abbreviations, such as the search for "BBC", "CNN" or "ABC". Here you might wish, that you can also search for those words.

What's wrong?

The exclusion of all words with 3 letters is caused by the following setting in the MySQL configuration:

ft_min_word_len = 4

This is the shortcut for "Full Text Minimum Word Length" and it specifies, that a word has to have a minimum length of 4 letters to be included in the Fulltext Index.

How to change the setting?

If you change the minimum word length for the full text search, it works well with searching for shorter words.

Therefore, first we look up the MySQL configuration file, that can be found in the directory "etc/mysql". The file is called "my.cnf" and we change the appropriate line in it to:

ft_min_word_len = 3

Next, we have to restart our MySQL server. In XAMPP, we can do this easily by using the Control Panel, orr in case of need, we can also restart the computer. Another possibility is to use the following command:

/etc/init.d/mysql restart

Finally, we have to re-create our fulltext index, since there are no words with three letters in it up to now. We do this through phpMyAdmin or the MySQL command:

REPAIR TABLE tab;

After the "REPAIR TABLE", our index will be disposed due to the changed settings and we can use the full-text search with words of 3 letters.

Note

The procedure described here, requires that we have the rights to modify the files and to restart the server. Unfortunately, for many hosting providers, there is no opportunity to do this, particularly not in cheap contracts. Usually, there it is not possible to change the minimum length - unless you have a good rapport with the local technicians.

However, in this case, you can use another trick, to find words with only three letters anyway. I have described a solution in this tip.