mysql match against with same score on different input

Question

I am trying to find the nearest match against a given name with mysql. The scoring is for the first two results the same, but looking at it I would rather get the second one as it fit's the name almost.

SELECT ID, name, MATCH (`name`) 
AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE) as `score` 
FROM cat_names cs 
WHERE MATCH (`name`) AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE) 
ORDER BY `score` DESC

The result:

Superocean Héritage 6.3744072914123535
Superocean Héritage 42 6.3744072914123535
Superocean Héritage 46 6.3744072914123535

Is there a way to tweak the query in order to retrieve the second result correctly?

Additionally the fulltext search returns a low quality score for exact matches:

Big Bang Aero Bang
9.445959091186523
Big Bang 44 mm
6.32852840423584
Big Bang Caviar
6.32852840423584
Big Bang Jeans
6.32852840423584
Big Bang Meca-10
6.32852840423584
Big Bang Sang Bleu
6.32852840423584
Big Bang Unico
6.32852840423584
Spirit of Big Bang
6.32852840423584
Big Bang 41 mm
6.32852840423584
Big Bang Broderie
6.32852840423584
Big Bang Ferrari
6.32852840423584
Big Bang King
6.32852840423584
Big Bang Pop Art
6.32852840423584
Big Bang Tutti Frutti
6.32852840423584
Big Bang
6.32852840423584

The exact match has the lowest score.

• Rick James: FULLTEXT is less than perfect. Perhaps in this case, "words" starting with digits are ignored?
• merlin: Can you think of a better solution then FULLTEXT?

Answer 1

Ah, you want a higher score for exact match? Instead of

ORDER BY `score` DESC

do

ORDER BY `name` = 'Superocean Héritage 42mm' DESC,
 `score` DESC

The = test will be 1 for an exact match, else 0. So, effectively, it overrules whatever the score returns.

(Alternative ways to phrase it involve IF or CASE).

• merlin: That solves part of my problem, thank you for that. The other problem is that in the first example the word contains "42mm" while the closest match would be "42" instead of the one without the number.
• Rick James: @merlin - OK, back to my first statement - Fulltext is less than perfect. There are a few 3rd party packages to do "fulltext" searches; they claim to be better and faster. (Sorry, I have no details.)
• merlin: Thank you Rick. That solved 80% of my problem which is good enough for now.