SQl MATCH AGAINST find results with only more than 3 characters in the result

MySQL SELECT

In MySQL, while searching column using MATCH AGAINST keywords then the result matches all the row with search character more than 3.

If you see the system variable ft_min_word_len, which specifies the minimum length of words to be indexed by full-text searching, it defaults to 4, so 3-letter words won’t be found by full-text searching.

Run show variables like ‘ft_%’; to confirm that the word length matches what you set it to.

ft_min_word_len = 4  

You can change the effect by updating the ft_min_word_len variable in the my.cnf MySQL configuration file:

[mysqld]
ft_min_word_len = 3

Then you must restart the server and rebuild your FULLTEXT indexes.

For avoiding this issue, in this case, you can still use LIKE:

‘%$keyword%’ LIKE CONCAT(‘%’, Column, ‘%’)

Or regular expression:

‘%$keyword%’ REGEXP Column

Leave a Reply

Your email address will not be published. Required fields are marked *