Category: Web Design Tweets/ReTweets

Using MySQL FULLTEXT Index for Short Words

  |   By  |  No comments

The other day I was presented with an issue. The issue had to do with search results returned from a MySQL MATCH (field) AGAINST (‘text’) query. The MATCH query requires the MySQL field to be a FULLTEXT index.Ā  The MATCH query can then return a weighted score, making it effective for this type of search ranking query.

The issue arose when searches contained short words, for example ‘key.’ MySQL’s FULLTEXT index has a setting for how long a word must be for inclusion into a FULLTEXT index. The setting is ft_min_word_len. MySQL’s default for ft_min_word_len is 4, so ‘key’ wouldn’t be included in the index. This led to searchesĀ  where ‘key’ would not return any results, but searches for ‘keyword’ would. Also, searches for ‘key lock’ would return results for ‘lock’, but not ‘key.’

Here is how one can change the value of ft_min_word_len. Open your my.cnf file or my.ini file on Windows.Ā  In the file locate the [mysqld] text. Below [mysqld] copy and paste the following MySQL option:

# Note that you need to rebuild your FULLTEXT index, after you have
# modified this value.
ft_min_word_len=3

If you are having problems saving your my.ini file right click on the text editor launcher and launch the editor as and Admin. Restart the MySQL server and run the following query to retrieve the setting:

“show variables like ‘ft_min%'”

Your ft_min_word_len should now be 3, and you need to reindex your table in order to search for words equal to ft_min_word_len.

We thought we’d share this in the hopes that it helps someone else out.