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 ConsultationRelated Articles
GDPR: General Data Protection Regulation
The European Union’s General Data Protection Regulation on data privacy came into effect on May 25, 2018. This new regulation policy applies…
Install Zoom on Ubuntu
You can install Zoom on Ubuntu in two ways: 1. Installing through Ubuntu Software install Firstly, we need to download the Zoom…
Steps for the Effective Application Development Process
Step 1: THE IDEA Assist customers with creating and building their unique idea. Share suggestions to the client and answer inquiries about…