MySQL limit query optimization _ MySQL

MySQL limit query optimization bitsCN.com

MySQL limit query optimization the following articles mainly introduce the specific content of MySQL limit query optimization. we all know that MySQL database optimization is very important. Limit is the most commonly used and optimized. MySQL limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.
10 data records are also retrieved.

Select * from yanxue8_visit limit, 10
Select * from yanxue8_visit limit 0, 10

It is not an order of magnitude.
There are also many five limit optimization guidelines on the Internet, which are translated from the MySQL Manual. they are correct but not practical. Today I found an article about limit optimization, which is quite good.
Instead of using limit directly, we first get the offset id and then use limit size to get data. Based on his data, it is much better to use limit directly. Here I use data in two cases for testing. (Test environment: win2033 + p4 dual-core (3 GHZ) + 4G memory MySQLlimit query)
1. when offset is small

1. select * from yanxue8_visit limit 10, 10

Run multiple times and keep the time between 0.0004 and 0.0005

Select * From yanxue8_visit Where vid> = (
Select vid From yanxue8_visit Order By vid limit 10, 1) limit 10

Run multiple times, and the time is kept between 0.0005-0.0006, mainly 0.0006
Conclusion: When the offset is small, it is better to use limit directly. This is obviously the cause of subquery.
2. when the offset value is large

Select * from yanxue8_visit limit, 10

Run multiple times and keep the time at around 0.0187

Select * From yanxue8_visit Where vid> = (
Select vid From yanxue8_visit Order By vid limit, 1) limit 10

Run multiple times, with a time of around 0.0061, only 1/3 of the former. It can be predicted that the larger the offset, the higher the latter.
Pay attention to correct your limit statement and optimize MySQL later.

MySQL optimization is very important. The other most commonly used and most needed optimization is limit. MySQL limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.
The above content is an introduction to the optimization of MySQLlimit queries. I hope you will have some gains.

BitsCN.com