If I run this query and print the score of each rows, they are always 1:
Here are some sample query results:
First | Last | Score
------------------------------
Jonathan | Bush | 1
Joshua | Gilbert | 1
Jon | Jonas | 1
And this is the query that I run:
SELECT First, Last, MATCH(First, Last) AGAINST ('Jon' IN BOOLEAN MODE) AS score
FROM users
WHERE MATCH(First, Last) AGAINST('Jon' IN BOOLEAN MODE)
ORDER BY score DESC;
The BOOLEAN MODE supports only binary answers, means 0 or 1 whether the search string appears in the column or not. To get a decimal result to calculate a weight, you have to use match-against on indexed columns.
You can use the boolean mode this way to get your wheight either:
SELECT *, ((1.3 * (MATCH(column1) AGAINST ('query' IN BOOLEAN MODE))) +
(0.6 * (MATCH(column2) AGAINST ('query' IN BOOLEAN MODE)))) AS relevance
FROM table WHERE ( MATCH(column1,column2) AGAINST
('query' IN BOOLEAN MODE) ) ORDER BY relevance DESC
The advantage of the boolean mode is that you can use it on non-indexed columns but only with 0,1 as result, the non-boolean mode returns a decimal result but can only be applied on indexed columns... see also here.
The accepted answer is partially correct. According to the MySQL docs, MATCH AGAINST can return floats. The database engine, if MyISAM, will only return 1 on match. InnoDB fulltext searches with MATCH AGAINST will return floats so that higher quality matches can be ordered by the match result.
Use the result of NATURAL MODE
as score:
SELECT First, Last, MATCH(First, Last) AGAINST ('Jon') AS score
FROM users
WHERE MATCH(First, Last) AGAINST('+Jon' IN BOOLEAN MODE)
ORDER BY score DESC;
Note: Give attention to the + operator. This one is only available in BOOLEAN MODE
.
Why I suggest NATURAL MODE
for sorting by relevance is that it returns for Jon Chris
:
Jon Chris | Jonas
Jon Martin Chris | Jonas
And BOOLEAN MODE
could return for +Jon +Chris
:
Jon Martin Chris | Jonas
Jon Chris | Jonas
This is because both words are found in BOOLEAN MODE
returning the score 2, but NATURAL MODE
adds more for the first entry because its a direct hit and/or direct following words returning the better search result.
Another reason the score may always be 1
(or 0
) is if the full-text indexes have suffered many changes: running OPTIMIZE TABLE my_table
will fix such degraded indexes, as documented in the Fine-tuning Full-text Search pages.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With