MYSQL limit Optimization-2
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
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
HI. Thank you for your article.
But there were something wrong with me.
If there were about 500,000 records in my table.
And I want to use LIKE and other sql syntax.
Like this:
select * from tbl
where col LIKE ‘%something%’ where id >= (
#subquery like yours#
) limit 30
I run up sql and it’s too slow…
Do you have any good ideas??
Hi!
I read your limit optimalizations, and testing with mysql pager applications, and very increased the speed. But this way only works, if the order column is a unique column. It doesn’t works well, if the order column is not unique, eg. a zip code. Is there any optimization to this situation?
Thank you!