CODEWITHSUNDEEP
mysqlfull-text-search
alex.bour
alex.bour

Reputation: 2976

Enhance MySQL search with FULLTEXT index

I would like to improve the results from my Fulltext query.

SELECT *, MATCH (winery) AGAINST ('chateau cheval blanc') AS score
FROM usr_wines
WHERE MATCH (winery) AGAINST ('chateau cheval blanc')
ORDER BY score DESC
...

The problem is the results shown are like this:

I think MySQL returns "chateau cheval blanc petit cheval" before the other one because it found 2 occurrences of "cheval" in the string. But for me, the second result should be displayed before the first because it matched more exactly what is queried.

I tried other things, such "WITH QUERY EXPANSION" and "IN BOOLEAN MODE" without success. I also read we can add signs (+) in the search to change behaviours.

Well, any help is welcome to obtain the appropriate behaviour.

Upvotes: 0

Views: 26

Answers (1)

Daniel E.
Daniel E.

Reputation: 2480

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

You just have to use IN BOOLEAN MODE with " to indicate that you want exact phrase. Example : 

SELECT *, MATCH (winery) AGAINST ('"chateau cheval blanc"'  IN BOOLEAN MODE) AS score
FROM usr_wines
WHERE MATCH (winery) AGAINST ('"chateau cheval blanc"'  IN BOOLEAN MODE)
ORDER BY score DESC

Upvotes: 1

Related Questions