About MySQL's sleep (N) function

Source: Internet
Author: User

Know that you can have this statement run for n seconds by executing select sleep (n) in MySQL:

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

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

With the help of the Sleep (N) function, we can capture in MySQL server's processlist a statement that executes quickly and easily to be seen to determine whether our program actually originated the statement on the server side. For example, when we are debugging, we want to determine whether the program actually makes a request to the server to execute the SQL statement, then we can see whether the statement appears by executing show processlist or by the Information_schema.processlist table. However, the sentence execution speed can be very fast, so it is difficult to determine whether the statement is actually executed by the above method. For example, the execution time of the following statement is 0.00 seconds, and the thread information flashes past, and it is impossible to detect.

?
1234567 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 to the statement, forcing the statement to stay for N seconds, for example:

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

The same condition returns the execution time of 1 seconds for the statement.

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

?
12 mysql> selectname from animals where name=‘pig‘;Empty set(0.00 sec)

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

?
12 mysql> selectsleep(1),name from animals where name=‘pig‘;Empty set(0.00 sec)

It is also important to note that after adding the sleep (N) function, the execution of the statement will be based on the number of records that satisfy the condition, and MySQL will stay N seconds for each record that satisfies the condition.
For example, the name like '%ger ' has three records

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

After adding sleep (1) to the statement, the total execution time for the statement is 3.01 seconds, which results in a 1-second stay for each record that satisfies the condition.

?

123456789 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)

Original: http://www.2cto.com/database/201412/365580.html

About MySQL's sleep (N) function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.