MySql query takes too long when sleep() is used in where clause

Question

I have read about sleep(n) function in MySql, which is supposed to sleep for n seconds and return 0 if uninterrupted or 1 if interrupted.

This works well if I use sleep() in select clause. For example, following query returns result after 10 seconds.

SELECT id, sleep(10) FROM versions WHERE id = 123

However, the query takes too long if I use sleep(10) in the where clause.

SELECT id FROM versions WHERE id = 123 OR sleep(10)=1

Any idea about why is it behaving like this?

Answer 1

In the first query it takes just 10 second sleep time while in the second one every id is being checked if it is 123 or not and if not it sleeps for 10 seconds.

In where clause using sleep is like checking it against each row in the database except the one where it matches the value 123 in your case.

Answer 2

It will sleep at every row for 10 seconds when your first condition is false.