Skip to content
SolutionsBased Local
Consult Now
Useful Links

Full text search against multiple columns with Relevance

·

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’

Ready to Solve Your Most Complex Technical Challenges?

Our senior advisors are ready to help you navigate the ever-evolving technology landscape with precision and expertise.

Schedule a Consultation

Related Articles