Quote:Change the full text index minimum word length with MySQL

The MySQL full text index by default only indexes words which are 4 characters or longer, which means on a blog like mine if you search for "PHP" nothing will be returned. This post shows how to change minimum word length in MySQL so words or 3 characters (or even 2 if you want) can be indexed as well.

Edit the MySQL configuration file my.cnf which is usually located on a *nix box at /etc/my.cnf or /etc/mysql/my.cnf and add the following line under the [mysqld] section to change the default to 3:

Code:

ft_min_word_len = 3

If the ft_min_word_len value is already in the file then change the number to the minimum length required.

After making this change the MySQL server must be restarted for it to take effect. It is not possible to change the size with a set query (e.g. "SET ft_min_word_len = 3" which will result in the error "#1193 - Unknown system variable 'ft_min_word_len'").

Now that the minimum word length has changed, and new or updated records will use the new minimum word length, but existing records will not be affected. To rebuild the full text index on a column for an example table called my_table, run this query:

Code:

REPAIR TABLE my_table QUICK;

I have seen comments by some people who have suggested that on large tables it may be faster to drop the index and create it again depending on the size, and also that the repairing it may mean the query cache is not flushed whereas dropping and re-indexing will solve this.
Note however that if you drop the index and then re-create it you may get SQL query errors on your website. As always it is advisable to test this sort of thing out on a development server configured in the same way as a production server before doing it to the production server to ensure you don't have any issues.