Explain mysql's classic limit usage and optimization examples
- 2020-06-01 11:08:53
- OfStack
Use 1
For example, this SQL, limit is followed by 2 pieces of data, and offset is read from the first piece.
Use 2
And this SQL, limit is going to read from the second one, read 1 message.
Don't confuse the two.
Use 3
From article 100 onwards - the record of the last one
Use 4
Equivalent to limit 0,15. Query results take the first 15 data usage 5
mysql lower version does not support limit offset
limit offset works in mysql 4.0 and above, but not in the older version of mysql 3.23
limit m offset n is equivalent to limit m,n
The optimization of limit
limit of mysql brings great convenience to paging, but when the data volume is large, the performance of limit drops sharply
Source: 1 mu 3 fen land blog
The optimization of MYSQL is very important. The other most commonly used and optimized is limit. mysql's limit brings great convenience to paging, but when the data volume is large, limit's performance declines sharply.
I'm going to take 10 pieces of data
It's not an order of magnitude.
Many of the five optimization guidelines for limit on the Internet are translated from the mysql manual, which is correct but not practical. Today I found an article about limit optimization, which is very good.
Instead of directly using limit, id of offset was first obtained and then limit size was directly used to obtain data. According to his data, it is significantly better than using limit directly. Here I use the data in two different ways. (test environment win2033+p4 dual-core (3GHZ) +4G memory mysql 5.0.19)
1. When offset was small.
Run for several times and keep the time between 0.0004 and 0.0005
Run for several times and keep the time between 0.0005 and 0.0006, mainly 0.0006
Conclusion: when the offset offset is small, it is better to use limit directly. This is obviously the cause of the subquery.
2. When offset is big.
Run for several times and keep the time around 0.0187
Run for several times, the time is kept at about 0.0061, only 1/3 of the former. It can be expected that the larger the offset, the better the latter.
In the future, we should pay attention to correct our limit statements and optimize mysql under 1
SELECT `keyword_rank`.* FROM `keyword_rank` WHERE (advertiserid='59') LIMIT 2 OFFSET 1;
For example, this SQL, limit is followed by 2 pieces of data, and offset is read from the first piece.
Use 2
SELECT `keyword_rank`.* FROM `keyword_rank` WHERE (advertiserid='59') LIMIT 2,1;
And this SQL, limit is going to read from the second one, read 1 message.
Don't confuse the two.
Use 3
select * from tablename < Conditional statements > limit 100,-1
From article 100 onwards - the record of the last one
Use 4
select * from tablename < Conditional statements > limit 15
Equivalent to limit 0,15. Query results take the first 15 data usage 5
mysql lower version does not support limit offset
limit offset works in mysql 4.0 and above, but not in the older version of mysql 3.23
limit m offset n is equivalent to limit m,n
The optimization of limit
limit of mysql brings great convenience to paging, but when the data volume is large, the performance of limit drops sharply
Source: 1 mu 3 fen land blog
The optimization of MYSQL is very important. The other most commonly used and optimized is limit. mysql's limit brings great convenience to paging, but when the data volume is large, limit's performance declines sharply.
I'm going to take 10 pieces of data
select * from yanxue8_visit limit 10000,10
select * from yanxue8_visit limit 0,10
It's not an order of magnitude.
Many of the five optimization guidelines for limit on the Internet are translated from the mysql manual, which is correct but not practical. Today I found an article about limit optimization, which is very good.
Instead of directly using limit, id of offset was first obtained and then limit size was directly used to obtain data. According to his data, it is significantly better than using limit directly. Here I use the data in two different ways. (test environment win2033+p4 dual-core (3GHZ) +4G memory mysql 5.0.19)
1. When offset was small.
select * from yanxue8_visit limit 10,10
Run for several times and keep the time between 0.0004 and 0.0005
Select * From yanxue8_visit Where vid > =(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
Run for several times and keep the time between 0.0005 and 0.0006, mainly 0.0006
Conclusion: when the offset offset is small, it is better to use limit directly. This is obviously the cause of the subquery.
2. When offset is big.
select * from yanxue8_visit limit 10000,10
Run for several times and keep the time around 0.0187
Select * From yanxue8_visit Where vid > =(
Select vid From yanxue8_visit Order By vid limit 10000,1
) limit 10
Run for several times, the time is kept at about 0.0061, only 1/3 of the former. It can be expected that the larger the offset, the better the latter.
In the future, we should pay attention to correct our limit statements and optimize mysql under 1