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

Today, I will talk to you about how to optimize limit query in MySQL. Many people may not know much about it. In order to make you understand better, the editor summarized the following content for you. I hope you can get something according to this article.

Also take 10 pieces of data.

Select * from yanxue8_visit limit 10000 select 10 select * from yanxue8_visit limit 0Magol 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.

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 MySQLlimit query)

1. When offset is relatively small

1.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 10J 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 From yanxue8_visit Where vid 1) limit 10 after reading the above, do you have any further understanding of how to optimize limit queries in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.