MySQL how do I 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,.....,
Case 
When a.title REGEXP [[:&lt;:]]Men's shoes[[:&gt;:]] Then 0
When a.title REGEXP [[:&lt;:]]Men's[[:&gt;:]] Then 1
When a.title REGEXP [[:&lt;:]]shoes[[:&gt;:]] 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
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 (&quot;men\'s t'shirts&quot;),(&quot;men\'s socks&quot;),(&quot;men\'s socks short&quot;)




SELECT * ,
(IF(MATCH (title)
        AGAINST (&quot;men\'s&quot; IN NATURAL LANGUAGE MODE) &gt; 0,1,0) 
        )+
        (IF(MATCH (title)
        AGAINST ('socks' IN NATURAL LANGUAGE MODE) &gt; 0,2,0) )
+
        (IF(MATCH (title)
        AGAINST (&quot;+men\'s +socks&quot; IN NATURAL LANGUAGE MODE) &gt; 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&lt;&gt;fiddle here
For much bigger searches and more possibilities, you could also look into elasticsearch