Search code examples
mysqlmatchmatch-against

Why does MATCH return integer relevance instead of float


I've looked into the boolean full text search documentation of mysql, and there is written, that MATCH will return a float value, together with an example. Everywhere else that I found reference to this states the same, MATCH will return a float point by relevance score.

But when I run this query

select 
match(j.name) against('banana* strawberr* coconut*' IN BOOLEAN MODE) as score
from juices j
order by score desc

I get either a 0 or a 1.

This are some of the lines with a 1 as score:

"Banana and strawberry flavor" 
"Apple and banana juice" 
"Coconuts with pickles"

In my understanding, the "banana and strawberry" line should get a higher score than the other two, since there are two words matching?


Solution

  • I just experienced this on one of my tables, where a different table was providing results as expected. Check to verify your table is not set to MyISAM (only returns 1 on match), you'll want InnoDB to get a float value returned on your matches so you can order by matched rank.