MySQL how to priorities single column by multiple keywords


I have a column of product title, I have to order by result by max word's matched from entered string

Select Id, tittle, description,.....,
When a.title REGEXP [[:<:]]Men's shoes[[:>:]] Then 0
When a.title REGEXP [[:<:]]Men's[[:>:]] Then 1
When a.title REGEXP [[:<:]]shoes[[:>:]] Then 2 END AS priority
From products 

Order by priority ASC

It's work fine, when string come in order, but I want order by max matched word count,
or assigned first priority to that record which have max word matches

Best Answer

Full text searches have their good site, but need a bit of learning, but are interesting for big tables with alot of text.

you can combine columns also for searches

Also check the manual

A small example how it works

CREATE TABLe t1 (title varchar(20),
          FULLTEXT (title))
insert into t1 VALUES ("men\'s t'shirts"),("men\'s socks"),("men\'s socks short")
(IF(MATCH (title)
        AGAINST ("men\'s" IN NATURAL LANGUAGE MODE) > 0,1,0) 
        (IF(MATCH (title)
        AGAINST ('socks' IN NATURAL LANGUAGE MODE) > 0,2,0) )
        (IF(MATCH (title)
        AGAINST ("+men\'s +socks" IN NATURAL LANGUAGE MODE) > 0.03,3,0))    as weight   
        FROM t1
title             | weight
:---------------- | -----:
men's t'shirts    |      1
men's socks       |      6
men's socks short |      6

db<>fiddle here For much bigger searches and more possibilities, you could also look into elasticsearch