3 5 Votes

MySQL: Change minimum word length for full text search

Tip by Stefan Trost | 13/06/2012 at 22:36

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.

ReplyPositiveNegative

Progger99

Show Profile | Message
Avatar
-414 Votes

Beautifully described. From me one more little tip on how to do it in another way:

SET GLOBAL ft_min_word_len=3;
REPAIR TABLE tab QUICK;

Also this command sets the minimum word length to 3 and re-creates the index afterwards. To switch the entire MySQL server, of course, the above method is better.
14/06/2012 at 16:45

ReplyPositive Negative
Reply

About the Author

Avatar AuthorYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile | Message

 

Related Topics

MySQL: Sort by Relevance
Question | 1 Answer
The Askingbox Search
Info | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.