Thursday, November 04, 2010

FULLTEXT search in MySQL DB does not return any result, but works fine in BOOLEAN mode

Well, that was a trap... First, I found that a simple query like
SELECT * FROM `articles` WHERE MATCH(title) AGAINST('jerusalem')
returns 0 rows on my development DB.
Next, I found that the same Native Language SQL query works just fine on the production DB with the real data.
Third, I discovered that the same query works just fine on both DBs when BOOLEAN search is used:
SELECT * FROM `articles` WHERE MATCH(title) AGAINST('jerusalem' IN BOOLEAN MODE)

The reason was that I got only two records in my development DB! So any Natural Language search failed there into the 50% 'common keywords' rule stated in MySQL manual:
A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given.

With just two rows any keyword was considered by MySQL as common! The obvious solution was adding one more empty row. And voila! - we got the search results.

It looks like this 50% rule can not be switched off by MySQL configuration. If you know how to switch it off drop me a note, would love to do that in my development environment.

No comments: