MySQL fulltext search scores all results with same value

Question

I have a table products with a column name where I want to do a full text search on it.

There is data on it:

name, other columns ...
...
BR26 AF-1, ...
BR26 AF-2, ...
BR26 AF-3, ...
BR26 AF-4, ...
BR26 AF-5, ...
BR26 AF-5, ...
BR26 AF-6, ...
BR26 AF-7, ...
BR26 AF-8, ...
BR26 AF-9, ...
BR26 AF-10, ...
BR26 AF-11, ...
BR26 AF-12, ...
BR26 Beton, ...
...

So I did:

ALTER TABLE products ADD FULLTEXT(name);

Now I prepared a query to find and return the matching results ordered by matching score relevance.

I have already tried with IN BOOLEAN MODE

SELECT *, MATCH (name) AGAINST ( 'BR26 AF-1' IN NATURAL LANGUAGE 
MODE ) as score FROM products WHERE MATCH (name) AGAINST ( 
'BR26 AF-1' IN NATURAL LANGUAGE MODE ) AND active='1' ORDER BY 
score DESC

Actual results and scores:

name, score, other columns ...
BR26 AF-5, 3.1776015758514404, ...
BR26 AF-2, 3.1776015758514404, ...
BR26 AF-3, 3.1776015758514404, ...
BR26 AF-4, 3.1776015758514404, ...
BR26 AF-1, 3.1776015758514404, ...
BR26 Beton, 3.1776015758514404, ...
BR26 AF-6, 3.1776015758514404, ...
BR26 AF-7, 3.1776015758514404, ...
BR26 AF-8, 3.1776015758514404, ...
BR26 AF-9, 3.1776015758514404, ...
BR26 AF-10, 3.1776015758514404, ...
BR26 AF-11, 3.1776015758514404, ...
BR26 AF-12, 3.1776015758514404, ...

Expected results and scores:

name, score, other columns ...
BR26 AF-1, <highest score>, ...
BR26 AF-2, <lower score>, ...
BR26 AF-3, <lower score>, ...
BR26 AF-4, <lower score>, ...
BR26 AF-5, <lower score>, ...
BR26 AF-6, <lower score>, ...
BR26 AF-7, <lower score>, ...
BR26 AF-8, <lower score>, ...
BR26 AF-9, <lower score>, ...
BR26 AF-10, <lower score>, ...
BR26 AF-11, <lower score>, ...
BR26 AF-12, <lower score>, ...
BR26 Beton, <lower score>, ...

What am I doing wrong to not get the expected result order? In my opinion "BR26 AF-1" would be a 100% match and there fore it should have the highest score.

score 0 · Answer 1 · edited Apr 30 '20 at 08:05

Can you try :

SELECT *, MATCH (name) AGAINST ( 'BR26 Beton' IN NATURAL LANGUAGE 
MODE ) as score FROM products WHERE MATCH (name) AGAINST ( 
'BR26 Beton' IN NATURAL LANGUAGE MODE ) AND active='1' ORDER BY 
score DESC

If you get a different result, I suspect it's because MySQL is using the hyphen (-) as a delimiter, and searching for BR26, AF, and 1 as three distinct words.

There are a few ways around this, including using boolean mode and wrapping your search term in double quotes. See this S.O. post: How to allow fulltext searching with hyphens in the search query

MySQL fulltext search scores all results with same value

1 Answers1