The optimization of MySQL is very important. The other most commonly used and most need to optimize is limit. MySQL's limit brings great convenience to paging, but when the amount of data is large, limit performance drops sharply. Same as 10 data
SELECT * from Yanxue8_visit limit 10000,10 and
SELECT * FROM Yanxue8_visit limit 0,10
is not a level of quantity.
There are also many five optimization criteria for limit, which are translated from MySQL manual, although correct but not practical. Today found an article written about limit optimization, very good.
Instead of using limit directly, you first get the ID of offset and then use the limit size directly to get the data. According to his data, it is significantly better to use 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 is relatively small time.
SELECT * FROM Yanxue8_visit limit 10,10
Run multiple times with 0.0004-0.0005 time remaining
Select * from Yanxue8_visit Where vid >= (
Select vid from Yanxue8_visit Order by vid limit 10,1
) Limit 10
Run multiple times, the time remains between 0.0005-0.0006, mainly 0.0006
Conclusion: The direct use of limit is better when the offset is smaller. This is clearly the cause of the subquery.
2. When the offset is large.
SELECT * FROM Yanxue8_visit limit 10000,10
Run multiple times and keep time at 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 remains around 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.
In the future to pay attention to correct their limit statement, to optimize the MySQL
Referrer Comments
The optimization of MySQL is very important. The other most commonly used and most need to optimize is limit. MySQL's limit brings great convenience to paging, but when the amount of data is large, limit performance drops sharply.
Mysql Limit performance optimizations (small offset vs. big offset)
