MySQL match against - IN BOOLEAN MODE?

2
votes

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?

1
phpmysql

1 Answers

4
votes

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)