[SOLVED] MySQL: Why score is always 1 in Fulltext?

MySQL: Why score is always 1 in Fulltext?

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;

Solution

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.