MySQL full text search in boolean mode order by relevance

Question

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?

score 2 · Answer 1 · edited Dec 04 '19 at 04:05

2

At first you want to expect ordering by relevance. You expect all infomation Can you try it SELECT * FROM TEST_BOOLEAN WHERE ID_1;

edited Dec 04 '19 at 04:05

Rob

14,746
28
47
65

answered Dec 04 '19 at 01:05

Mehmet Kutan

79
4

score 0 · Accepted Answer · answered Dec 04 '19 at 01:32

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.

MySQL 55.7.23, and your solution is the one I had to resort to, even though I was hoping for a more elegant possibility. — , Dec 04 '19 at 01:39

MySQL full text search in boolean mode order by relevance

2 Answers2