How to disable mysql fulltext stopwords?

Author: seven September 15, 2009

Easy! :) A while ago, I ranted about fulltext stopwords in mysql.

To repeat – 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 .cnf file, restart mysql engine and rebuild indexes:

ft_stopword_file = ""

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

Happy searching! :)

Author
seven
CEO/CTO at Nivas®
Neven Jacmenović has been passionately involved with computers since late 80s, the age of Atari and Commodore Amiga. As one of internet industry pioneers in Croatia, since 90s, he has been involved in making of many award winning, innovative and successful online projects. He is an experienced full stack web developer, analyst and system engineer. In his spare time, Neven is transforming retro-futuristic passion into various golang, Adobe Flash and JavaScript/WebGL projects.

    12 thoughts on “How to disable mysql fulltext stopwords?”

  • Awesome, thanks for the help. I was seriously losing my mind.

  • hi, i try to put
    ft_stopword_file = “” on my mysql configuration file ( my.ini) , and its not working. can u help me??
    im using WAMP server version 1.7.2
    thank u.

  • a note from the manpage:

    Note: FULLTEXT indexes must be rebuilt after changing this variable or the contents of the stopword file. Use REPAIR TABLE tbl_name QUICK.

  • Thanks for the tip, I also found that it needs to be in the [mysqld] section of the my.cnf file to be taken into account after restart.

  • Great tip..!
    For me it worked like a charm..
    The only thing I had to do is to add the line in my.cnf and restart MySQL..

    ;)

  • Hey Nivas Guys,

    Thanks for the great tip – I’d been tearing my hair out trying to find out why my website search function was not finding anything containing the word “best”. Searched Google for “mysql full text ignore stop words” and this was the 3rd non- dev.mysql.com result.

    Thanks Again.

  • LOL! Glad it worked out for you. Yes, we are using our site as a playground for SEO tests, and some of them actually work. :)

  • Thanks dude, works like a magic.

  • Hello, I never downloaded and installed MySQL files locally. I have shared hosting service and don’t have private virtual server. How can I edit my.cfn file or where can I find it? P.S. I use phpMyAdmin. Thanks

  • hi,

    can you pls tell me where to find the wamp stopwords file ?

    regards

  • Thanks a lot!!!

    In my case, i just created a new “Parameter Groups” from standard in RDS (Amazon AWS) with ft_stopword_file set to “/dev/null”
    For everyone with the same problem: After that, just modify the parameter in the RDS instance, reboot RDS and use the

    REPAIR TABLE tbl_name QUICK

    Regards

  • Leave a Reply to Leonardo Cancel reply

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