MySQL limit query optimization

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

MySQL limit query optimization in the following article is mainly about the specific content of the MySQL limit query optimization is introduced in this paper, we all know that the MySQL database optimization is very important. The other is the most common and most need to be optimized is limit. MySQL limit brings great convenience to the page, but when a large amount of data, the performance of limit fell sharply.
  Also take 10 data

select * from yanxue8_visit limit 10000,10
select * from yanxue8_visit limit 0,10

  It is not a level number.
  The Internet has a lot on the limit five optimization criteria, are translated from MySQL manual, although correct but not practical. Today I found an article written about limit optimization, very good.
  This paper does not directly use limit, but the offset access to the ID and then directly use the limit size to access data. According to his data, significantly better than the direct use of limit. Here I use of specific data are divided into two kinds of test. (test environment win2033+p4 dual core (3GHZ) +4G memory MySQLlimit query)
  1, When offset is small

1.select * from yanxue8_visit limit 10,10

  Run multiple times, time is kept between 0.0004-0.0005

Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1 ) limit 10

  Run multiple times, time maintained at between 0.0005-0.0006, mainly 0.0006
  Conclusion: when the offset offset is small, the direct use of limit better. This is obviously why query sub.
  2, When offset is large

select * from yanxue8_visit limit 10000,10

  Run multiple times, time maintained at around 0.0187

Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10000,1 ) limit 10

  Run multiple times, time maintained at around 0.0061, only the former 1/3. It can be expected the higher the offset, the latter is better.
  Pay attention to correct your own limit statement, optimization at the MySQL.


  MySQL optimization is very important. The other most commonly used most in need of optimization is limit. MySQL limit brings great convenience to the page, but when a large amount of data, the performance of limit fell sharply.
  More related content is query optimization for MySQLlimit, hope you can harvest.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Debra at December 12, 2013 - 1:41 PM