MySQL fulltext search not working for some words like 'house'

5
votes

I've setup a full text index on a small selection of records across 3 fields (tried a combination of the 3 as well and had the same result), and some words return results fine, but certain ones such as 'house' and 'australia' don't (interestingly, 'australian' and 'home' do).

It seems like odd behaviour. If I add "WITH QUERY EXPANSION" I get results, but they aren't the most relevant now.

Anyone got any idea why this is? Otherwise I'm going to have to resort to using LIKE searches, and I'd prefer to have relevancy included.

1
mysqlfull-text-search

1 Answers

10
votes

It could be a couple of things:

  • MySQL has a default list of 'stop words' that aren't included in a full text search - http://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html . 'house' and 'australia' don't seem to be in that list, but other words you search for might be affected by it.
  • Any word that appears in over 50% of the rows or is 3 letters or in less in length in the database is also considered a stop word. The 50% thing is particularly easy to be caught out by if you're just testing the full text search out on a table with only a few rows in.

If you search Google for "mysql stop words" you'll find a lot more about it, as it's one of those things that catches a lot of people out.