Detailed description of mysql limit classic usage and optimization instances

Source: Internet
Author: User
Tags mysql manual

Usage 1
 Copy codeThe Code is as follows:
SELECT 'keyword _ rank '. * FROM 'keyword _ rank' WHERE (advertiserid = '59') LIMIT 2 OFFSET 1;
 
For example, in this SQL statement, two pieces of data are followed by the limit statement, and the offset statement is followed by 1st pieces of data.

Usage 2
 Copy codeThe Code is as follows:
SELECT 'keyword _ rank '. * FROM 'keyword _ rank' WHERE (advertiserid = '59') LIMIT 2, 1;
 
In this SQL statement, limit reads one message starting from 2nd.

Don't confuse the two.

Usage 3
Copy codeThe Code is as follows:
Select * from tablename <Condition Statement> limit 100,-1
 
Starting from 100th-the last record

Usage 4
 Copy codeThe Code is as follows:
Select * from tablename <Condition Statement> limit 15
 
Equivalent to limit. Use 5 to retrieve the first 15 pieces of data from the query result

Mysql earlier versions do not support limit offset

Limit offset can run normally in mysql 4.0 or a later version. It is invalid in mysql 3.23 of the earlier version.

Limit m offset n is equivalent to limit m, n

Limit Optimization

Mysql limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.

Source: One-acre blog

MYSQL optimization is very important. The other most commonly used and most needed optimization is limit. Mysql limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.

10 data records are also retrieved.
 Copy codeThe Code is as follows:
Select * from yanxue8_visit limit, 10

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 article about limit optimization, which is quite good.

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.
 Copy codeThe Code is as follows:
Select * from yanxue8_visit limit 10, 10
 
Run multiple times and keep the time between 0.0004 and 0.0005
 Copy codeThe Code is as follows:
Select * From yanxue8_visit Where vid> = (

Select vid From yanxue8_visit Order By vid limit 10, 1

) Limit 10

Run multiple times, and the time is kept between 0.0005-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.
 Copy codeThe Code is as follows:
Select * from yanxue8_visit limit, 10
 
Run multiple times and keep the time at around 0.0187
 Copy codeThe Code is as follows:
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.

Pay attention to correct your limit statement and optimize mysql later.

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.