Anyone who is facing problem in doing MySql full text search against multiple columns with Relevance can follow the below steps:
Suppose we have to search the records having (xbox controller) in title and description and you want that keywords searched in title should be having high relevance then the keywords searched in description, then query should be like this:
SELECT *,
MATCH (Title) AGAINST (‘ +baby +toy’) AS relevance,
MATCH (Description) AGAINST (‘ +baby +toy’) AS relevance1,
MATCH (Title, Description) AGAINST (‘ baby toy’) AS relevance2,
FROM tblMaster WHERE
MATCH (Title, Description) AGAINST (‘ baby toy’)
ORDER BY relevance DESC, relevance1 DESC, relevance2 DESC LIMIT 0,10
Below are some of example of how we can use various combinations for keywords to be searched:
a) ‘ +baby +toy’ will find rows that contain both words.
b) ‘ baby toy’ will find rows that contain at least one of the two words.
c) ‘ +baby toy’ will find rows that contain the word “xbox ”, but rank rows higher if they also contain “controller”.
d) ‘+baby -toy’ will find rows that contain the word “xbox” but not “controller”.
Also keep in mind that these column names in the database should be Indexed with type ‘FULLTEXT’
