Mysql: Change the full text index minimum word length

So you are trying to do a fulltext search but your searches for “cat” or “dog” always return zero results, even though you have articles all about cats and dogs. By default the minimum search text length for full index searches is 4 characters, anything less than that returns nothing, zip, nada, jack-all.  You need to change the minimum word length.

This can throw people using something like Magento whose product types or names are short, such as “bag”,”cup”, or “mug”.  The problem isn’t actually in Magento but a server setting. You might be kind of stuck if you are using a shared hosting environment – you can contact your hosting service and ask nicely.

You will need access to the my.cnf  (mysql configuration file) on your server. I use Centos and this is just located in /etc/my.cnf

You will need to add (or modify if it already exists) the line

ft_min_word_len = 3

The mysql server needs to restart before the change will take place (or restart the whole server if needs be).  The existing index will still be populated with the data for the previous index size so you will need to either repair (rebuild) the index or truncate the index and get them rebuilt.

REPAIR TABLE my_index_table QUICK;

OR

TRUNCATE my_index_table;

If you use query caching, and running the repair table does not appear to work then truncate and then reindex by calling the repair command.

Facebook Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Apply your human brain cells and complete this highly complicated maths problem *