Mysql select by best match with like

Question

I have this query:

SELECT `id` FROM `accounts` 
WHERE AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
OR AES_DECRYPT(`email`, :salt) LIKE CONCAT('%',:host)

I have 2 records in that table:

id     email
1      [e-mail]
2      [e-mail]

When I'm running this query like this:

SELECT `id` FROM `accounts` 
WHERE AES_DECRYPT(`email`, '123') = CONCAT_WS('@','test','test.com') 
OR AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com')

I get this as a result:

id     email
2      [e-mail]
1      [e-mail]

Question: What I want is this: I want to have the best match as the first result, without using fulltext search. Is this possible, if so, how can I do this?

Answer 1

This code should suit you:

SELECT `id` FROM `accounts` 
    WHERE AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
    OR AES_DECRYPT(`email`, :salt) LIKE CONCAT('%',:host)
    ORDER BY LENGTH(`email`) - LENGTH(REPLACE(`email`, :host, '')) - LENGTH(REPLACE(`email`, :mailbox, '')) DESC

Answer 2

You can readily order the results by the number of matches:

SELECT `id`
FROM `accounts`
WHERE AES_DECRYPT(`email`, '123') = CONCAT_WS('@', 'test', 'test.com') OR 
      AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com')
ORDER BY ( (AES_DECRYPT(`email`, '123') = CONCAT_WS('@', 'test', 'test.com')) +
           (AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com'))
         );

This will work for your example.

Answer 3

To get records in a specific order, use an ORDER BY clause.

SELECT `id` FROM `accounts` 
WHERE AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
OR AES_DECRYPT(`email`, :salt) LIKE CONCAT('%',:host)
order by AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host) desc;

Here we are using a MySQL special. A boolean expression that evaluates to TRUE results in 1. A boolean expression that evaluates to FALSE results in 0. In another DBMS you could write this instead:

order by 
 case when AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
      then 1 else 0 end desc;