I ran into a problem using mysqls MATCH()...AGAINST() today.
Because I guess it might be easier to adress my problem I created a small SQLFiddle.
I fully understand, why "Foo Bar Foo" is more relevant then "Foo Bar" in the way this Select statment works. But what I want is that "full matching" terms are treated with a higher relevancy then "full matching and a little bit more" terms.
title relevance
Foo Bar 0.046829063445329666
Foo Bar Foo 0.031219376251101494
Foo 0
Bar 0
Is this result possible without using "union" to combine two statements?
Answer (thanks to Gordon Linoff):
SELECT *
FROM FOOBAR
WHERE title LIKE '%Foo Bar%'
ORDER BY (CASE WHEN title LIKE 'Foo Bar%' THEN 1 ELSE 0 end) DESC,
MATCH (title) AGAINST ('Foo Bar' IN BOOLEAN MODE) ASC
You can do what you want in the order by. One method would be to count the number of different words that match and then within each group, order by relevance ascending:
SELECT fb.*,
MATCH (title) AGAINST ('+Foo +Bar' IN BOOLEAN MODE) AS relevance
FROM FOOBAR fb
ORDER BY ((case when title like '%Foo%' then 1 else 0 end) +
(case when title like '%Bar%' then 1 else 0 end)
) desc,
relevance asc
This does what you want for the example data. It may not work in general, because the relevance calculation has other factors besides the number of times a word matches and the length of the string.