MySQL, Stopwords, and You!

For the past few days, I’ve been looking into one of those problems that makes you feel literally crazy.

A customer complained that when searching their store for the phrase “Keep Trying” no products showed up.   Since this is a Shopp site, I looked at the index to verify the index had those words (it did) and then turned on debugging to grab the actual query being used.

When you remove the cruft, this is the query it was using:

This query returned 0 rows.

While this query:

Returned several, and the expected ones at that.

After dropping the index and rebuilding it.  Repairing the table.  Reading all of the documentation on BOOLEAN MATCH searches and generally starting to feel like a crazy person, I finally decided to file a ticket with Site5.

Next day, I get back this response:

This took quite a lot of troubleshooting but I eventually found that MySQL has a list of stop words and in this particular command you appear to be using all stopwords. The words “keep” “trying” “try” are all stopwords. Please see here:

http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

They are going to disable the stopword list, as this site uses lots of phrases in which these words would appear.

So, kudos to Site5 (that’s an affiliate link), and let that be a lesson to you:  When MySQL seems incapable of finding simple words, those words may be stopwords and you’re wasting your time.