The Correct Way to Use MySQL SLEEP() Command
-
Use the
SLEEP()
Command in MySQL -
Use the
DO SLEEP()
Command in MySQL -
the Difference Between MySQL
SLEEP
andDO SLEEP
-
the Behavior of MySQL
SLEEP()
in a Query Statement
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.
- Create a database in MySQL and create a new table in this database.
- Insert some records into the table.
- Use
SELECT *, SLEEP (n) from <table_name>
. Wheren
is the sleep time, andtable_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 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