Detailed Mysql limit classic usage and optimization examples _mysql

Source: Internet
Author: User
Tags mysql manual mysql version
Use a
Copy Code code as follows:

SELECT ' Keyword_rank '. * from ' Keyword_rank ' WHERE (advertiserid= ' a ') LIMIT 2 OFFSET 1;

For example, the SQL, followed by the limit is 2 data, offset is from the 1th read from the beginning.

Usage Two
Copy Code code as follows:

SELECT ' Keyword_rank '. * from ' Keyword_rank ' WHERE (advertiserid= ') LIMIT 2, 1;

And this sql,limit is followed by the 2nd read, read 1 messages.

Don't confuse these two.

Usage Three
Copy Code code as follows:

SELECT * FROM tablename < conditional statement > limit 100,-1

Start after 100th-record of the last article

Usage Four
Copy Code code as follows:

SELECT * FROM tablename < conditional statement > limit 15

Equivalent to limit 0,15. Query results take the first 15 data usage five

MySQL low version does not support limit offset

Limit offset works well in MySQL version 4.0 and is not available in the older version of MySQL 3.23

Limit m offset n is equivalent to limit m,n

Optimization of Limit

The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit drops dramatically.

Source: A three-acre blog

The optimization of MySQL is very important. The other most common and most need to optimize is limit. The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.

It's also taking 10 data.
Copy Code code as follows:

SELECT * FROM Yanxue8_visit limit 10000,10

SELECT * FROM Yanxue8_visit limit 0,10

is not a quantitative level.

There are also a lot of five optimization guidelines on limit, which are translated from MySQL manual, although correct but not practical. Today I found an article to write about limit optimization, very good.

Instead of using limit directly, we first get the ID of offset and then use limit size directly to get the data. According to his data, it is significantly better than using limit directly. Here I use the data in two different situations to test. (Test environment WIN2033+P4 dual core (3GHZ) +4g memory MySQL 5.0.19)

1, offset relatively small time.
Copy Code code as follows:

SELECT * FROM Yanxue8_visit limit 10,10

Run multiple times, keeping the time between 0.0004-0.0005
Copy Code code as follows:

Select * from Yanxue8_visit Where vid >= (

Select vid from Yanxue8_visit order by vid limit 10,1

) Limit 10

Run multiple times, keeping the time between 0.0005-0.0006, mainly 0.0006

Conclusion: The direct use of limit is more excellent when offset is smaller. This is obviously the reason for the subquery.

2. When the offset is big.
Copy Code code as follows:

SELECT * FROM Yanxue8_visit limit 10000,10

Run more than once and keep the time around 0.0187
Copy Code code as follows:

Select * from Yanxue8_visit Where vid >= (

Select vid from Yanxue8_visit order by vid limit 10000,1

) Limit 10

Run several times, the time remained at about 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.

Later should pay attention to correct their own limit statements, optimize the MySQL

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.