I am having a problem with my search relevance and filter using fulltext (match against) and i will explain below.
I have a table called Songs which contains over 1.5M records, this table contains many columns which 2 of them are name and artist, those 2 columns are fulltext indexed.
when i search a title and song matching exactly the values it shows a low score, and for me it doesnt make any sense for example.
DATA:
name | artist
------------- | ------------
Glad You Came | Boyce Avenue
When i execute the query below it brings:
SELECT name, artist,
MATCH(name, artist) AGAINST('+glad +you +came +Boyce +Avenue' IN BOOLEAN MODE) AS score
FROM live_lyric.songs
WHERE MATCH(name, artist) AGAINST('+glad +you +came +Boyce +Avenue' IN BOOLEAN MODE)
name | artist | score
------------- | ------------ | ------------------
Glad You Came | Boyce Avenue | 54.727073669433594
I am searching for exact match, how come it can be 54.7??
If I remove the + sign like
SELECT name, artist,
MATCH(name, artist) AGAINST('glad you came Boyce Avenue' IN BOOLEAN MODE) AS score
FROM live_lyric.songs
WHERE MATCH(name, artist) AGAINST('glad you came Boyce Avenue' IN BOOLEAN MODE)
i am going to have a similar response with a lot of other records where are not relevant at all.
For me the search with the '+' sign should be near 100 why it is not, and how can i improve it?