Optimization of MySQL limit in PHP

10 data records are also retrieved.
Select * From yanxue8_visit limit, 10
And
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 articleArticleI wrote something about limit optimization, which is very good. Original address: http://www.zhenhua.org/article.asp? Id = 200

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 MySQL 5.0.19)

1. When the offset value is small.
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, with the time between 0.0005 and 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, time remaining 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.