How to Change MySQL FULLTEXT Index Minimum and Maximum Length of Words

MySQL database has built-in full-text search capability that allows SQL queries to perform search quickly using MySQL database engine. The full-text search capability depends on the FULLTEXT index which is been created on various table fields or columns. However, some search queries may not return the experted results or datasets, even though the data is verified to exists on full-text search, especially on short keywords or search terms.

The problem probably lies on the default minimum length of words indexed by FULLTEXT index which is set as 4 characters. Thus, when attempting to search via full-text index, the short words are not indexed, and hance not return in search results.

By default, the minimum value for length of word to be indexed in FULLTEXT index is four characters, while the default maximum length of words to be indexed in FULLTEXT index is varies depending on version of mySQL server. To increase the number of words indexed and searchable, especially shorter words such as three-character words, administrator can reduce the minimum or increase the maximum length of words to be indexed by using ft_min_word_len and ft_max_word_len system variables.

For example, for FULLTEXT index to index the three-character words, or 3-letter text, add in the following line to set the lower value for ft_min_word_len variable under [mysqld] section to an option file, i.e. my.cnf:

[mysqld] ft_min_word_len=3

Save the file, and then restart the MySQL server. After changing either value of full-text variable, which both affects indexing, the FULLTEXT indexes must be rebuilt. The following command to do a quick repair operating is sufficient to rebuild the indexes:

mysql> REPAIR TABLE tbl_name QUICK;

Note that each and every table that contains any FULLTEXT index must be repaired with SQL command above. Also do not use myisamchk (unless you have also defined the full-text parameter values in [myisamchk] section) to perform the rebuilt as the full-text parameters are only known to mysqld server. If rebuild is not done, queries for the table may yield incorrect results, and modifications to the table will cause the server to see the table as corrupt and in need of repair.