107

In relation to my other question today I am wondering how to use MySQL's SLEEP(duration) correctly.

From what I gathered reading MySQL Dev forums and very vague description in MySQL Docs I can't use it this way:

SELECT ...
SLEEP(1); /* wait for a second before another SELECT */
SELECT ...

So what is it good for then?

Community
  • 1
  • 1
Michal M
  • 9,322
  • 8
  • 47
  • 63
  • 8
    based on reading of the mysql docs, i would have tried it your way first, i never seem to walk away with useful information after reading the mysql docs, thanks to Konerak below for the very simple answer .... – Landon Sep 23 '13 at 23:48
  • @Landon, The answer by Iroh is the better one. – Pacerier Apr 12 '15 at 14:09

2 Answers2

129

If you don't want to SELECT SLEEP(1);, you can also DO SLEEP(1); It's useful for those situations in procedures where you don't want to see output.

e.g.

SELECT ...
DO SLEEP(5);
SELECT ...
Uncle Iroh
  • 5,748
  • 6
  • 48
  • 61
  • 8
    This should be the accepted answer. The output of `sleep` is always 0, Why would anyone bother selecting the output of `sleep` for..... – Pacerier Apr 12 '15 at 14:08
  • 2
    @Pacerier: this answer appeared 5 years later, but the example uses DO, which is indeed more elegant than SELECT. The big clue was showing SLEEP itself was not a command, but rather a function, which both answers show, but feel free to mark this answer as accepted. – Konerak Sep 05 '16 at 07:23
  • 2
    @Pacerier one reason to select the output of sleep is to put it in a query to test locking and transaction isolation levels in concurrent queries, for example. – Barry Kelly Nov 03 '16 at 15:58
  • Also, if you don't want to see the output you can add a join or bogus nested query like: select 1 from (select sleep(1)) a; – Martin May 10 '18 at 04:04
  • 1
    @Pacerier Wrong. It can output `1`. https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_sleep – Cleroth Sep 29 '20 at 20:50
  • As mentioned above `SELECT sleep(n)` returns `1` if it's interrupted. This output is particularly useful when testing timeouts. For eg. with `SET SESSION max_execution_time=` – Anubis Feb 02 '22 at 08:44
116
SELECT ...
SELECT SLEEP(5);
SELECT ...

But what are you using this for? Are you trying to circumvent/reinvent mutexes or transactions?

Konerak
  • 39,272
  • 12
  • 98
  • 118
  • 61
    I use this command to confirm slow query logging is working as it should. – jeffatrackaid Sep 04 '14 at 14:34
  • 10
    Should also be useful for testing asynchronous queries at the application layer. – Programster Nov 08 '15 at 11:01
  • 6
    @MicalM There is no reason to reassign "accepted" answer. This answer is also correct and was so for 5 years before I chimed in. I just added that other one because I thought it added value to the question posed. – Uncle Iroh Mar 10 '16 at 18:55
  • 1
    I use this command so the dump import won't start until after the script decompression finishes. It kicked in some time while I was sleeping last night. – MatrixManAtYrService Jan 23 '18 at 15:51