Recently we had a problem with search in our support system that use osTicket system. Any searches containing less or equal than 3 characters ended up with an empty result. We thought that the problem is somewhere in code with limitations, but we were wrong. The code is good. ;) After a while we finally realized what the problem is.
In MySQL database exist some limitations with FULLTEXT indexes in a tables. The minimum and maximum lengths of words to be indexed are defined by the „ft_min_word_len“ and „ft_max_word_len“ system variables. When we check in MySQL database the minimum length of word which can be indexed we found number 4.
The default minimum value is four characters; the default maximum is version dependent. If you change either value, you must rebuild your FULLTEXT indexes.
More about minimum and maximum lengths of words which can be indexed and rebuilding FULLTEXT indexes you can find at official MySQL site.
How you can check minimum and maximum lengths of words which can be indexed?
First at all, we need to open MySQL databases (you can open in browser or console). Then we can execute following SQL query:
SHOW VARIABLES LIKE 'ft_min_word_len'
As result of query we will get the number of minimum lengths of words.
To set up new minimum lengths of words which can be indexed you need to open mysql config file, search for [mysqld] section and write ft_min_word_len=3.
Your part of code in file need to look like below
Number 3 is just example, you can write whatever you suit the best. For maximum lenght of words principle is same. Instead of ft_min_word_len enter ft_max_word_len.
MySQL default location of configuration file for Unix, Linux and Mac OS X users is:
and for Windows users:
NOTE: Because „ft_min_word_len“ and „ft_max_word_len“ are read only variables, we can’t set it with PLAIN SQL.
Next step is to restart the server.
Finally we need to rebuild the FULLTEXT indexes.
To rebuild the indexes in this case, it is sufficient to do a QUICK repair operation. Like before we need to open MySQL databases (you can open in browser or console), and then to run query
REPAIR TABLE tbl_name QUICK
where tbl_name is a name of your table in DB.
That’s all, your advanced search with four or less characters now works.