Home >Backend Development >PHP Tutorial >Optimization of MYSQL limit under php_PHP tutorial
Optimization of MYSQL limit under php_PHP tutorial
- WBOYOriginal
- 2016-07-21 15:54:08975browse
Also taking 10 pieces of data
select * from yanxue8_visit limit 10000,10
and
select * from yanxue8_visit limit 0,10
are not at the same level of quantity.
There are also many five optimization guidelines for limit on the Internet, which are all translated from the MySQL manual. Although they are correct, they are not practical. Today I found an article about limit optimization, which is very good. Original address: http://www.zhenhua.org/article.asp?id=200
Instead of using limit directly, the article first obtains the offset id and then directly uses limit size to obtain data. According to his data, it is obviously better than using limit directly. Here I specifically use data for testing in two situations. (Test environment win2033+p4 dual-core (3GHZ) +4G memory mysql 5.0.19)
1. When the offset is relatively small.
select * from yanxue8_visit limit 10,10
Run multiple times, the time remains between 0.0004-0.0005
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
After multiple runs, the time remains between 0.0005-0.0006, mainly 0.0006
Conclusion: When the offset offset is small, it is better to use limit directly. This is obviously the reason for the subquery.
2. When the offset is large.
select * from yanxue8_visit limit 10000,10
Run multiple times, the time remains around 0.0187
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10000 ,1
) limit 10
Run multiple times, the time remains at around 0.0061, only 1/3 of the former. It can be expected that the larger the offset, the better the latter.