MYSQL limit Optimization-2

Posted by rueben

March 9, 2008

The MYSQL optimization is very important. Other most commonly used also most need to optimize are limit. mysql limit has brought enormous convenient to the paging, but the data quantity one is big, the limit performance suddenly drops.

Similarly takes 10 data

select * from temp limit 10000,10
and
select * from temp limit 0,10

It is not a quantity rank.

Now many about limit five optimized criterion, is from the mysql handbook ,You may have a look at this article about Mysql limit:http://www.easemarry.com/blog/mysql-limit-optimization/

In the article uses limit directly, but first gains to offset id then uses limit size to gain the data directly. According to his data, is friends with obviously in uses limit directly. Here my concrete service data is divided two kind of situations to carry on the test.

1st, offset small time.

select * from temp limit 10,10

Repeatedly running , the time maintains between 0.0004-0.0005

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

Repeatedly running , the time maintains between 0.0005-0.0006

Conclusion: offset is small, uses limit to be superior directly. This is the sub-inquiry reason obviously.

2nd, offset great time.

select * from temp limit 10000,10

Repeatedly running , the time maintains between 0.0185-0.0190

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

Repeatedly running , the time maintains between 0.0061-0.0065,Only the former 1/3. May estimate that offset is bigger, the latter is more superior.
http://www.easemarry.com/blog