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.

Related Articles

• MySQL Workbench - Visual Database Deign Tool Free Download
• Aqua Words Free Word Puzzle Game from Realore Studios at GAOTD
• 64-bit (x64) Windows Vista SP1 (6001.1800) tcpip.sys Auto Patcher to Fix Event ID 4226 and Unlock Increase Maximum Connections Allowed
• How to Change the Display Language (Including Welcome Screen) in Windows Vista
• How to Change Icon of Shortcut in Windows Vista
• How to Activate or Change to Windows Vista Ultimate Extras Glass or Pearl Sound Scheme
• How to Change the Microsoft Office Excel Protect Sheet Password
• Download Windows Search 4.0 Preview
• Make Searches with Natural Language on Powerset Search Engine
• How to Enable PAE in x86 32-bit Windows Server 2003 and 2000 to Use Large 4GB or More RAM Memory

This entry was posted on Tuesday, April 8th, 2008 at 9:50 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.