[SOLVED] MySQL full text search in boolean mode order by relevance

MySQL full text search in boolean mode order by relevance

I am trying to construct a full text search in boolean mode in MySql.

First I tried this as I read that if you use WHERE it will automatically return results by relevance:

SELECT DISTINCT word1, lang1, lang2, row_type FROM translations WHERE MATCH (word1) AGAINST ('$search' IN BOOLEAN MODE)

So ordering by relevance is not happening. Then I tried the following:

$sql = "SELECT *, MATCH(word1) AGAINST ('$search' IN BOOLEAN MODE) AS relevance FROM `translations` WHERE MATCH(word1) AGAINST ('$search' IN BOOLEAN MODE) ORDER BY relevance DESC";

This just ranks the matches by the number of words matched and then lists them by the ID number. There is no relevance search beyond the number of words matched.

For example if I search for the term "red car" it would return:

1. red car is nice
2. red car
3. the car has a very nice color
4. car

I would expect the expression "car" to come out before "the car has a very nice color"

The relevance numbers when using BOOLEAN MODE are whole numbers representing the number of words matched.

When I use NATURAL LANGUAGE MODE the relevance numbers are fine decimal numbers and the relevance search works fine.

How can I achieve a true relevant result list in BOOLEAN MODE?

Solution

What is your MySQL version?

I do remember that it was no no relevance in boolean mode. It was just TRUE/FALSE (1/0). I used to use boolean mode in WHERE clause and natural language mode in SELECT/ORDER clause.

You can also try to do something like this:

SET @search = 'red car';

SELECT
      *
    , MATCH(word1) AGAINST (@search IN BOOLEAN MODE) AS relevance
FROM
    translations
WHERE
    MATCH(word1) AGAINST (@search IN BOOLEAN MODE)
ORDER BY
      relevance DESC
    , LENGTH(word1) ASC
;

So if two records have the same relevance, the shorter one would be first.