MySQL limit paging optimization method sharing _mysql

It's also taking 10 data.
SELECT * from Yanxue8_visit limit 10000,10 and
SELECT * FROM Yanxue8_visit limit 0,10
is not a quantitative level.
There are also a lot of five optimization guidelines on limit, which are translated from MySQL manual, although correct but not practical. Today I found an article to write about limit optimization, very good.
Instead of using limit directly, we first get the ID of offset and then use limit size directly to get the data. According to his data, it is significantly better than using limit directly. Here I use the data in two different situations to test. (Test environment WIN2033+P4 dual core (3GHZ) +4g memory MySQL 5.0.19)
1, offset relatively small time.
SELECT * FROM Yanxue8_visit limit 10,10
Run multiple times, keeping the time between 0.0004-0.0005
Select * from Yanxue8_visit Where vid >= (
Select vid from Yanxue8_visit order by vid limit 10,1
) Limit 10
Run multiple times, keeping the time between 0.0005-0.0006, mainly 0.0006
Conclusion: The direct use of limit is more excellent when offset is smaller. This is obviously the reason for the subquery.
2. When the offset is big.
SELECT * FROM Yanxue8_visit limit 10000,10
Run more than once and keep the time around 0.0187
Select * from Yanxue8_visit Where vid >= (
Select vid from Yanxue8_visit order by vid limit 10000,1
) Limit 10
Run several times, the time remained at about 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.
Later should pay attention to correct their own limit statements, optimize the MySQL
recommended by People review
The optimization of MySQL is very important. The other most common and most need to optimize is limit. The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.