By default MySQL FULLTEXT search will not search for words that are less than 4 characters in length. For many things this can be great as many words less than 4 characters are generally stop words such and often aren’t valuable, for example words such as, the, and, if, on, etc. Although this isn’t always the case and in certain circumstances small words are actually really important. For example, let’s say you’re looking for a new developer job using any of the following technologies, ios, php, C#, .Net, ASP, etc. In these cases, the default MySQL FULLTEXT search default minimum characters actually prohibit results being found, which isn’t a great user experience. As such, you may want to update your MySQL FULLTEXT search functionality to enable smaller words to be searched for while quality results are being identified.
Edit My.cnf File
The my.cnf file on your web server generally sits under /etc/my.cnf and allows you to customise your MySQL configuration. You can edit this file by logging into your web server using SSH, navigate to the correct folder and run the command pico my.cnf which will allow you to edit the file.
Now you need to add the following line of code at the bottom of the fie which will allow MySQL FULLTEXT search to search for words with a minimum word length of 2 characters, ft_min_word_len=2
Once you have completed this, save the file.
Restart MySQL
Next you need to restart the MySQL service using the following command, service mysql restart which will ensure that the MySQL service will use the new configuration data once it has restarted. Should you experience a problem restarting MySQL, then remove the code you just added in the my.cnf file or comment out the code with a # at the start of the line. If you’re not aware, the following commands also exist which can come in handy should the MySQL service not restart smoothly, service mysql stop and service mysql start.
Rebuild All MySQL FULLTEXT Indexes
Finally you need to rebuild all your MySQL FULLTEXT indexes that you are using on your database. If you only need the smaller words to be searched on specific tables, then you clearly don’t need to rebuild the ones that aren’t relevant, although it can be handy as this could save you hours of debugging further down the line if different tables are using different minimum word lengths. Login to your phpMyAdmin if you’re running this on your web server to access the MySQL database then run the following command on which ever table you want to update, REPAIR TABLE <TableName> QUICK;
All done! Now your MySQL FULLTEXT Search will be able to search on smaller words than previously.
Some additional resources that can come in handy include official documentation about how to fine tune MySQL FULLTEXT searches.
Michael Cropper