The Correct Way to Use MySQL SLEEP() Command

Habdul Hazeez Jun 22, 2022
  1. Use the SLEEP() Command in MySQL
  2. Use the DO SLEEP() Command in MySQL
  3. the Difference Between MySQL SLEEP and DO SLEEP
  4. the Behavior of MySQL SLEEP() in a Query Statement
The Correct Way to Use MySQL SLEEP() Command

This article teaches you two ways to show how to use the MySQL sleep command. The first involves using SLEEP(), and the second is DO SLEEP().

You’ll also learn the behavior of SLEEP() in a query statement.

Use the SLEEP() Command in MySQL

The following is how to use SLEEP() in MySQL.

SELECT SLEEP(10);

From the SQL above, the MySQL SLEEP() function takes the number 10 as a parameter. This number determines the number of seconds SLEEP() will occupy in the server thread.

With SELECT SLEEP(10);, MySQL cannot do other work for ten seconds. That’s because it’ll use up the thread stack.

The following is the output of the previous sleep command.

+-----------+
| SLEEP(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

Use the DO SLEEP() Command in MySQL

DO SLEEP() works the same as SLEEP(), but it will not return any result. The following is how it works.

DO SLEEP(10);

Like SLEEP(), when you execute the previous SQL, it will wait for 10 seconds before proceeding. However, it returns an empty result shown below.

Query OK, 0 rows affected (10.01 sec)

the Difference Between MySQL SLEEP and DO SLEEP

The difference between SLEEP() and DO SLEEP() is that SLEEP() returns a result while DO SLEEP() doesn’t.

the Behavior of MySQL SLEEP() in a Query Statement

When used in a query statement, the duration of SLEEP() depends on the returned records. If the table contains no records, MySQL will not sleep.

However, if the table has records, the sleep time is n * table records where n is the sleep time.

To prove this, do the following.

  1. Create a database in MySQL and create a new table in this database.
  2. Insert some records into the table.
  3. Use SELECT *, SLEEP (n) from <table_name>. Where n is the sleep time, and table_name is your table name.

For example, in the following, the table users has three records. When we run the SQL code, MySQL will wait for 30 seconds.

That’s number_of_records * sleep_time which is 3 * 10.

SELECT *, SLEEP(10) FROM users;

Output:

+---------+----------+-----------+
| user_id | username | SLEEP(10) |
+---------+----------+-----------+
|       1 | Delft    |         0 |
|       2 | Stack    |         0 |
|       3 | Website  |         0 |
+---------+----------+-----------+
3 rows in set (30.02 sec)
Habdul Hazeez avatar Habdul Hazeez avatar

Habdul Hazeez is a technical writer with amazing research skills. He can connect the dots, and make sense of data that are scattered across different media.

LinkedIn