2019-09-14, 17:21
Problem: When searching on this forum, we cannot search for gps or any of the six 3-character words at the top of our status pages.
Reason: The default minimum search term in MySQL is four characters.
Solution: A very simple change to a configuration file can change it from to four to three.
This article at https://www.electrictoolbox.com/mysql-fu...ord-length tells how.
Can we please do this? "gps" is not the only 3-character word that we've needed to search for.
Reason: The default minimum search term in MySQL is four characters.
Solution: A very simple change to a configuration file can change it from to four to three.
This article at https://www.electrictoolbox.com/mysql-fu...ord-length tells how.
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.
Can we please do this? "gps" is not the only 3-character word that we've needed to search for.