Menu Close

How to disable mysql fulltext stopwords?

by nivas.hr
The stopword list is a list of most used words in english language. Common words such as “some”, “little”, “let” or “then” are stopwords and do not match if present in the search string.

Basically, fulltext searching for any of the stopwords would return (almost) all the entries, so MySQL ignores those words to reduce result pollution and for efficiency.

If the word starts with stopword, that word is returned, however if your word IS stopword, you are out of luck. Here is a list of build in stopwords in MySQL 5.0/5.1. Yeah, well this is so very exciting, but whole universe DOES NOT speak and write just english.

Last time we had problems with stopwords, there was no way to disable them without recompiling the mysql engine from the source. On production servers where you need to stay away from recompiling custom builds for maximum compatibility that was no go.

Today we again encountered stopword problem. Client complained that he cannot find video from artist “Let 3” or artist “Little Boots” (Victoria Hesket). To my surprise, in no time – I’ve found out a prefectly legal and easy to apply solution. Just add this to your /etc/mysql/my.cnf file under [mysqld] section:

ft_stopword_file = ""

restart mysql engine :

service mysql restart

and rebuild indexes:

REPAIR TABLE tbl_name QUICK

I addition to that, if you haven’t allready, lower the min word to 2 (ft_min_word_len=2) to be able to search for 2 letter words.

Happy searching! 🙂

Posted in Linux, News, Web Development

Leave a Reply

Your e-mail address will not be published. Required fields are marked *