About MySQL's SLEEP (N) function, mysqlsleep Function

About MySQL's SLEEP (N) function, mysqlsleep Function
We all know that you can run this statement for N seconds by executing select sleep (N) in MySQL:

mysql> select sleep(1);+----------+| sleep(1) |+----------+|        0 |+----------+1 row in set (1.00 sec)

The execution time returned to the client shows that the wait time is 1 second.

With the sleep (N) function, we can capture statements that are not easy to be viewed quickly in MySQL Server PROCESSLIST to determine whether our program has initiated the Statement on the Server. For example, if we want to determine whether the program actually initiates a request to execute SQL statements to the Server during debugging, we can check whether the statements appear by executing the show processlist or the information_schema.processlist table. However, the statement execution speed may be very fast, so it is difficult to determine whether the statement is actually executed through the above method. For example, if the execution time of the following statement is 0.00 seconds, the thread information will flash and be invisible.

mysql> select name from animals where name='tiger';+-------+| name  |+-------+| tiger |+-------+1 row in set (0.00 sec)

In this case, you can add a sleep (N) function in the statement to force the statement to stay for N seconds to view the background thread. For example:

mysql> select sleep(1),name from animals where name='tiger';+----------+-------+| sleep(1) | name  |+----------+-------+|        0 | tiger |+----------+-------+1 row in set (1.00 sec)

In the same condition, the execution time returned by this statement is 1.0 seconds.

However, this method has the precondition that the specified number of seconds will be stopped only when the specified condition record exists. For example, if the query condition is name = 'pig', the result indicates that the record does not exist, the execution time is 0.

mysql> select name from animals where name='pig';Empty set (0.00 sec)

Under such a condition, even if the sleep (N) function is added, the execution of the statement will flash, for example:

mysql> select sleep(1),name from animals where name='pig';Empty set (0.00 sec)

Note that after the sleep (N) function is added, the specific duration of statement execution depends on the number of records meeting the conditions, mySQL will stay for N seconds for each matching record.
For example, there are three records for name like '% ger '.

mysql> select name from animals where name like '%ger';+-------+| name  |+-------+| ger   || iger  || tiger |+-------+3 rows in set (0.00 sec)

Then, after the sleep (1) function is added to the statement, the total execution time of the statement is 3.01 seconds. It can be concluded that MySQL has stayed for 1 second for each record meeting the condition.

mysql> select sleep(1),name from animals where name like '%ger';+----------+-------+| sleep(1) | name  |+----------+-------+|        0 | ger   ||        0 | iger  ||        0 | tiger |+----------+-------+3 rows in set (3.01 sec)