Order SQL by strongest LIKE?

Question

I have the following query:

SELECT * FROM table_name
WHERE (col_1 LIKE '%$keyword%'
    OR col_2 LIKE '%$keyword%'
    OR col_3 LIKE '%$keyword%')
  AND .... <some optional filters> ... 

is there a strategy to sort based on the most relevant results?

Answer 1

If you mean that col_1 is more relevant than col_2 and so on, then :

select *
      ,case when col_1 like '%$keyword%' then 1
            when col_2 like '%$keyword%' then 2
            when col_3 like '%$keyword%' then 3
       end as [priority]
  from table_name
 where col_1 like '%$keyword%'
    or col_2 like '%$keyword%'
    or col_3 like '%$keyword%'
 order by [priority]

If you meant with the most column match then :

select *
      ,(case when col_1 like '%$keyword%' then 1 else 0 end) +
      ,(case when col_2 like '%$keyword%' then 1 else 0 end) +
      ,(case when col_3 like '%$keyword%' then 1 else 0 end) as [priority]
  from table_name
 where col_1 like '%$keyword%'
    or col_2 like '%$keyword%'
    or col_3 like '%$keyword%'
 order by [priority] desc

Answer 2

When you talk about "relevance", you really want natural language search, which is supported by MySQL full-text searches. The syntax is different than normal like queries, and you need to add a special index to the table, but ordering by relevance is possible this way.

Here's how MySQL computes relevance (from the link):

When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

To create a full-text index on an existing table, use the FULLTEXT modifier:

CREATE FULLTEXT INDEX index_name
ON table_name (col1, col2, col3)

Then you can perform a query like this to retrieve results in order of relevance:

SELECT * FROM table_name
WHERE MATCH (col1,col2,col3)
AGAINST ('keyword' IN NATURAL LANGUAGE MODE);