Shulou(Shulou.com)05/31 Report--

This article mainly talks about "the optimization method of MYSQL limit". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the optimization method of MYSQL limit.

The optimization of MYSQL is very important. The other thing that is most commonly used and needs optimization most is limit. Mysql's limit brings great convenience to paging, but when the amount of data is large, the performance of limit drops sharply.

Also take 10 pieces of data.

Select * from yanxue8_visit limit 10000 no. 10

And

Select * from yanxue8_visit limit 0pl 10

It's not a quantitative level.

On the Internet, there are also many of the five optimization guidelines for limit, which are translated from the mysql manual, which are correct but not practical. Today, I found an article about limit optimization, which is very good. Original address: http://www.zhenhua.org/article.asp?id=200

In this paper, instead of using limit directly, we first get the id of offset and then directly use limit size to get the data. According to his data, it is obviously better than using limit directly. Here I use the data to test in two cases. (test environment win2033+p4 dual core (3GHZ) + 4G memory mysql 5.0.19)

1. When offset is relatively small.

Select * from yanxue8_visit limit 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

Multiple runs, the time is kept 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 reason for the subquery.

2. When offset is big.

Select * from yanxue8_visit limit 10000 no. 10

Run many times and keep the time at about 0.0187

Select * From yanxue8_visit Where vid > = (

Select vid From yanxue8_visit Order By vid limit 10000,1

) limit 10

Run many times, the time is kept at about 0.0061, only the former 1 / 3. It can be expected that the larger the offset, the better the latter.

At this point, I believe that you have a deeper understanding of "MYSQL limit optimization method", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.