MySQL Full-text search on multiple columns

Question

So let me describe my problem. I am trying to implement fulltext search on multiple columns. I have a datatable with cd records. It contains column about artist and column about the name of the record. So I'm searching on both of them. The problem is that when I'm searching for example with query "queen", I get better score for artist "queen jane" then for artist "queen". This is my search logic:

SELECT * , 
MATCH(artist) AGAINST ('queen') as score_artist,
MATCH(cd) AGAINST ('queen') as score_title 
FROM records
WHERE MATCH(artist, cd) AGAINST ('queen') 
order by score_artist+score_title DESC, artist asc, cd asc;

and for example score for "queen" and "queen jane" are:

band        score_artist        score_title     
queen jane  4.803966045379639   4.078868865966797
queen       4.803966045379639   4.0335259437561035

So again, is there any other way to specify that matches that fully match are more important as the one that just partially match.

Thanks

http://stackoverflow.com/questions/792875/which-sql-query-is-better-match-against-or-like may help — ItalianStallion, Sep 03 '14 at 21:29

score 0 · Answer 1 · answered Sep 04 '14 at 08:35

I solved my prblem by adding following code to my query:

CASE WHEN artist = 'death' 
THEN <some value>
ELSE 1 
END AS prx_artist,

I added this after SELECT *, so now I use prx_artist to multiply score_artist. is just value with which you want to multiply score_artist if current artist value equals to the one in the query string.

MySQL Full-text search on multiple columns

1 Answers1