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)

Like this:

select * from mytable where name like "%someval%"; 

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

Comments

[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.

[Lev Nahar]

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.