DEV Community

Dimitrios Desyllas
Dimitrios Desyllas

Posted on

How I can order the returned results by relevance in mysql or mariadb when I use like statement in where?

As I ask upon https://dba.stackexchange.com/q/338422/118215

I search in this table:

mytable:
---
id PK Biginteger Autoincrement
name varchar(255)
Enter fullscreen mode Exit fullscreen mode

Like this:

select * from mytable where name like "%someval%"; 
Enter fullscreen mode Exit fullscreen mode

And I want to sort my result using the closest match towards someval. How I can do this?

Top comments (2)

Collapse
 
ccoveille profile image
Christophe Colombier

If you were using a MySQL full search index with MATCH, maybe.

Here you are using "LIKE" so it's an exact match modulo the collation.

Maybe you could look around SOUNDEX.

But I'm unsure you will be able to sort then.

Collapse
 
lnahrf profile image
Lev N. • Edited

You can use the ORDER BY clause which will sort the returned records. Note that ORDER BY when used on non-PK fields or text fields is costly and might make your query inefficient.

I recommend creating an index using the same clause required (closest match to someval) and only then using ORDER BY.