mysqlsearchindexingfull-text-searchrelevance

How can I manipulate MySQL fulltext search relevance to make one field more 'valuable' than another?


Suppose I have two columns, keywords and content. I have a fulltext index across both. I want a row with foo in the keywords to have more relevance than a row with foo in the content. What do I need to do to cause MySQL to weight the matches in keywords higher than those in content?

I'm using the "match against" syntax.

SOLUTION:

Was able to make this work in the following manner:

SELECT *, 
CASE when Keywords like '%watermelon%' then 1 else 0 END as keywordmatch, 
CASE when Content like '%watermelon%' then 1 else 0 END as contentmatch,
MATCH (Title, Keywords, Content) AGAINST ('watermelon') AS relevance 
FROM about_data  
WHERE MATCH(Title, Keywords, Content) AGAINST ('watermelon' IN BOOLEAN MODE) 
HAVING relevance > 0  
ORDER by keywordmatch desc, contentmatch desc, relevance desc 

Solution

  • Actually, using a case statement to make a pair of flags might be a better solution:

    select 
    ...
    , case when keyword like '%' + @input + '%' then 1 else 0 end as keywordmatch
    , case when content like '%' + @input + '%' then 1 else 0 end as contentmatch
    -- or whatever check you use for the matching
    from 
       ... 
       and here the rest of your usual matching query
       ... 
    order by keywordmatch desc, contentmatch desc
    

    Again, this is only if all keyword matches rank higher than all the content-only matches. I also made the assumption that a match in both keyword and content is the highest rank.