MySQL short-circuit OR

Question

table  
id | word

SELECT id FROM table WHERE 
word = "hello" OR 
word = "ello" OR 
word = "llo" OR 
word = "lo" OR 
word = "o"

I need to get only first id that is found. If not first - then second. If not second - then third ....
Is it possible to get only first OR that is in the database, without checking all of them?

Answer 1

You can't do it that way, but the easiest way to handle your request would be to

ORDER BY length(word) DESC

This would return the longest (best-matching) results first, so you can use LIMIT on it.

Answer 2

add a LIMIT 1 to the end

Answer 3

ORDER BY length(word) DESC LIMIT 1

Answer 4

Using Union may help.

mysql> select * from words;
+----+-------+
| id | word  |
+----+-------+
|  1 | hello | 
|  2 | ello  | 
|  3 | llo   | 
|  4 | lo    | 
|  5 | o     | 
+----+-------+
5 rows in set (0.00 sec)


SELECT id
FROM   (SELECT id
        FROM   words
        WHERE  word = 'hello'
        UNION
        SELECT id
        FROM   words
        WHERE  word = 'ello'
        UNION
        SELECT id
        FROM   words
        WHERE  word = 'llo'
        UNION
        SELECT id
        FROM   words
        WHERE  word = 'lo'
        UNION
        SELECT id
        FROM   words
        WHERE  word = 'o') w
LIMIT  1; 

Answer 5

I don't get what you want but I'll give it a shot.

You should probably use REGEXP.

This will match any word ending with o
SELECT name FROM pet WHERE name REGEXP 'o$' ORDER BY length(name) DESC LIMIT 1;

http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp