Mysql limit classic usage and Optimization _ MySQL-mysql tutorial

Classic mysql limit usage and optimization of bitsCN.com

Classic mysql limit usage and optimization

Usage 1

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

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

Select * from tablename <条件语句> Limit 100,-1

Starting from 100th-the last record

Usage 4

Select * from tablename <条件语句> 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.

Select * from yanxue8_visit limit, 10 and

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.

Select * from yanxue8_visit limit 10, 10

Run multiple 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 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.

Select * from yanxue8_visit limit, 10

Run multiple times and keep the time at around 0.0187

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.

BitsCN.com