How and When To Use SLEEP() Correctly in MySQL?

Pre-requisites: MySQL – Introdution

MySQL has many useful but yet unexplored features. SLEEP() is one of these. SLEEP is a query that pauses the MySQL process for a given duration. If we give NULL or negative value, query gives a warning or an error. If there is no issue or error, query resumes process with 0 return value.  The duration given to SLEEP should always be less than wait_timeout, interactive_timeout to avoid error or warning in the execution.

1. wait_timeout: It is a variable which shows how many seconds MySQL will wait before killing any idle process
2. interactive_timeout: It is a variable which shows how many seconds MySQL will wait before killing any active process

Syntax:

SELECT SLEEP(duration);

DO SLEEP(duration);

Where ‘duration‘ can be any positive integer number. It is given in seconds.

Why Do We Need SLEEP()?

1. To delay query logging:

In this case duration for sleep should be more than time taken for the execution of the query that we want to log. For Example:

Query:

SELECT * FROM user;

Let’s assume this query takes 1000 seconds to execute. So we will have SLEEP() as:

Query:

SELECT SLEEP(1010);

2. To Test Asynchronous Queries:

We use SLEEP in this scenario to pause the execution until all the previous queries are done.

Output:

In the output image given above, we are using SELECT SLEEP(10);  Here 10 is the time given in seconds. So the execution will be paused for 10 seconds and then we will see the result for the query. You can clearly see the Query took 10.0069 seconds for the overall execution.