MySQL how do I priorities single column by multiple keywords
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