Detailed description of mysql limit classic usage and optimization instances

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.