Explain mysql's classic limit usage and optimization examples

  • 2020-06-01 11:08:53
  • OfStack

Use 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

Related articles: