On the function of sleep (N) functions of MySQL and its application scene

All know that by executing select sleep (n) in MySQL, you can have this statement run for N seconds:

Mysql> Select Sleep (1);
+----------+
| Sleep (1) |
+----------+
| 0 |
+----------+
1 row in Set (1.00 sec)

The execution time returned to the client shows a wait of 1 seconds

Using the Sleep (N) function, we can capture a statement in MySQL server's processlist that executes quickly and easily, to determine whether our program actually initiated the statement on the server side. For example, when debugging, we want to make sure that the program does actually launch a request to the server to execute the SQL statement, we can either execute show processlist or the Information_schema.processlist table to see if the statement appears. Often, however, the execution of a statement can be very fast, which makes it difficult to determine whether a statement is actually executed. For example, the following statement has an execution time of 0.00 seconds, and the thread information is flashed through without realizing it.

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

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

Mysql> Select Sleep (1), the name from animals where name= ' tiger ';
+----------+-------+
| Sleep (1) | name |
+----------+-------+
| 0 | Tiger |
+----------+-------+
1 row in Set (1.00 sec)

Same condition the execution time returned by the statement is 1 seconds.

However, the use of this method is conditional, and only specify the condition of the existence of a record will stop the specified number of seconds, such as the query condition is name= ' pig ', the result indicates that the record does not exist, execution time is 0

Mysql> select name from animals where name= ' pig ';
Empty Set (0.00 sec)

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

Mysql> Select Sleep (1), name from animals where name= ' pig ';
Empty Set (0.00 sec)

Also note that when you add the sleep (N) function, the execution of the statement depends on the number of records that meet the criteria, and MySQL stops for N seconds for each record that satisfies the condition.
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)

So when you add sleep (1) to the statement, the total execution time of the statement is 3.01 seconds, and MySQL stops for 1 seconds for each record that satisfies 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)

Summary: This function I am currently mainly used in debugging SQL statements, the application project has not been used for the time being.