MySQL: Why score is always 1 in Fulltext?

Question

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;

Despairing

Answer 1

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.

Beware

thanks very much for your answer, the problem was that one of my columns weren't fulltext indexed so It didn't work without boolean mode. But another problem without boolean mode is that I can't search in words, so I need to write down the whole word to get a result for example with boolean mode I can search for Jo and I will get the results what includes Jo in them, and this is because in BOOLEAN MODE I can use the * etc. operators. – Despairing

Hm, sounds tough :-) Have you thought about using Apache Lucene (lucene.apache.org)? There's also an API for php (if you use it). Lucene creates its own indexes and you can perform every query you dream of ;-) – Beware

Well I'm using Node.js, mysql is fine, I already done what I want now before in one of my PHP applications. I don't really know now how should I do it now. And I don't really understand what are you doing in your answer :)) Why are you multiplying there? – Despairing

Ok, I think there are simply some limitations within every technology, so you get to those of mysql :-) If you use node.js you might have to effort to switch to the usage of lucene... lucene is only an addition to mysql. You do not have to use it for everything, only for the "hard" queries – Beware

but then I would need to split my database into to system? – Despairing

No, Lucene goes on top of your database... it creates its own "lucene-local" index you can perform queries on. All your data is still stored in mysql – Beware

@CIRK let us continue this discussion in chat – Beware

Old question but still: Lucene is independent of MySQL. And boolean mode returns floats on full-text-indexed columns. – Jacintajacinth

Answer 2

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.

Terti

Answer 3

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.

Ashmead

Answer 4

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.

Jacintajacinth