MySQL match against - IN BOOLEAN MODE?

Question

Hi there,

I'm using PDO to execute a MATCH AGAINST query.

The following returns nothing:

SELECT title, author, isbn, MATCH(title, isbn) AGAINST (:term) AS score FROM books WHERE MATCH(title, isbn) AGAINST (:term) ORDER BY score DESC LIMIT 0,10

Where as this returns perfectly:

SELECT title, author, isbn, MATCH(title, isbn) AGAINST (:term) AS score FROM books WHERE MATCH(title, isbn) AGAINST (:term IN BOOLEAN MODE) ORDER BY score DESC LIMIT 0,10

Could anyone tell me why IN BOOLEAN MODE is making such a difference, and whether or not I should be using it in my query?

Thanks!

Answer 1

The second query is running as a "natural language search" as that is the default when no natural language search type is specified. This type of search filters additionally filters out words that are present in 50% or more of the rows automatically.

"IN BOOLEAN MODE" does do this additional filtering, and thus, may return matches if you are searching on a common term.

Whether or not you should be using a boolean search depends on what the specifics of your situation and cannot be determined without more information. However, some considerations may include, size of the input data set vs how large of a matching dataset you want returned and whether you want to return results for words that occur frequently.

(Ref: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html)