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?
Two very important concepts when using full text search are stop words and minimum word length.
"Stop words" are words that are not included in the index, so they are simply ignored in the query and in the text. In your example, 'came' is a stop word. The documentation is here.
The minimum word length (usually 4) is the smallest word that is considered (see here). So, you query is "+glad +you +came +Boyce +Avenue" but it is really "+glad +Boyce +Avenue". I think this explains the issue with the score.