Mysql Limit performance optimizations (small offset vs. big offset)

Source: Internet
Author: User
Tags mysql manual

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.