Find best matching row in MySQL (InnoDB)

Question

I have the following test string engine/mail/key and a table which looks like this:

+-------------+
| query       |
+-------------+
| engine      |
| engine/pdf  |
| engine/mail |
+-------------+

I want to find the best matching row. Best match is specified by the most matching characters from the start of the string/row.

I have constructed a RegExp, but it of course matches all rows and won't give me any info about which matched the most chars/parts.
Regexp: ^engine(/mail(/key)?)?

I had an other idea about using MySQL's FIND_IN_SET function like this:

`FIND_IN_SET(query,'engine,engine/mail,engine/mail/key')`

And order the result by it's output.

That would work, but it's not in any way a nice solution. Does anybody have an better idea about this?

Answer 1

Just use LIKE, but the other way around to what your probably used to.

select query
from table1
where 'engine/mail/key' like concat(query,'%')
order by length(query) desc
limit 1

Results:

mysql> select * from query;
+-------------+
| query       |
+-------------+
| engine      | 
| engine/pdf  | 
| engine/mail | 
+-------------+
3 rows in set (0.00 sec)

mysql> select query from query 
       where 'engine/mail/key' like concat(query,'%') 
       order by length(query) desc 
       limit 1;
+-------------+
| query       |
+-------------+
| engine/mail | 
+-------------+
1 row in set (0.01 sec)