MYSQL limit Optimization
select * from table LIMIT 5,10; #return to 6-15th line
select * from table LIMIT 5; #return to 0-5 line
select * from table LIMIT 0,5; #return to 0-5 line
Performance optimization:
In MySQL5.0 limit high performance.
1.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) limit 100;
2.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;
Similarly takes 90000 latter 100 records, 1st quickly 2nd quick?
1st has preoccupied the first 90001 records, takes is biggest a ID value to take outset marking, then uses it to be possible fast localization next 100 record
Very obvious 2nd wins. Before looking like limit probably looks like me, not completely imagines such makes the entire table scanning to return to limit the offset+length strip record, this way limit compares MS-SQL Top the performance to enhance many.
Actually 2nd definitely may revise:
Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;
Uses the 90000th record directly ID, does not need to undergo the Max operation, because like this makes theoretically the efficiency this high somewhat, but does not look at the effect nearly in the actual use, because itself locates the ID returns is 1 record, Max does not need the operation to be able nearly to obtain the result.
But, since MySQL has limit to be possible the positive governing to take out the record the position, why not simply uses Select * From cyclopedia limit 90000,1? To be how could it not be more succinct? wanted mistakenly, to try like this to know that the result was: 1 row in set (8.88) sec, what kind, suffices scary, let me remember yesterday compared this in 4.1 also to have ” the high score “. Select * should better not use casually, must in line with use anything, chooses anything the principle, the Select field to be more, the field data quantity is bigger, the speed is slower. Have above 2 kind of paging mode which kind 1 strong been more than Shan Xiezhei, although looks like the number of times which probably inquires to be more some, but in fact has received in exchange for the highly effective performance by the small price, was worth.
The 1st kind of plan may use in MS-SQL similarly, moreover possibly is best. Because locates the initial sector depending on principal linkage ID to be always quickest.
Select Top 100 * From cyclopedia Where ID>=(
Select Top 90001 Max(ID) From (
Select ID From cyclopedia Order By ID
) As tmp
)
But no matter realizes the way to PROCEDURE or code, the bottleneck lies in MS-SQL TOP always to throughout return to the first N record, this kind of situation when the data quantity is not big feels not deeply, but if hundred and thousand of ten thousand, the efficiency can definitely be low. Comparatively MySQL limit has superiority many, the execution:
Select ID From cyclopedia limit 90000
Select ID From cyclopedia limit 90000,1
result respectively is:
90000 rows in set (0.36) sec
1 row in set (0.06) sec
But MS-SQL can only use Select Top 90000 ID From the cyclopedia execution time is 390ms, carries out the similar operating time also to be inferior to MySQL 360ms.
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, Rueben !
I tested described optimization methods on my large database(near 1,2GB data) and here is the result:
mysql> SELECT id FROM ltable WHERE id>(SELECT id FROM ltable LIMIT 120000,1) limit 5;
+——–+
| id |
+——–+
| 581099 |
| 581100 |
| 581101 |
| 581102 |
| 581103 |
+——–+
5 rows in set (38.70 sec)
mysql> SELECT id FROM ltable LIMIT 120001,5;
+——–+
| id |
+——–+
| 581099 |
| 581100 |
| 581101 |
| 581102 |
| 581103 |
+——–+
5 rows in set (20.31 sec)
we can see that the method don’t work correctly… What do u think ?
Nice trick! Thanks!
SELECT *
FROM forum_posts AS pa
LEFT JOIN forum_posts_text AS pb ON pa.post_id = pb.post_id
LEFT JOIN forum_users ON user_id = post_poster
WHERE post_topic_id = ‘450′
ORDER BY pa.post_id ASC
LIMIT 5475 , 15
versus
SELECT *
FROM forum_posts AS pa
LEFT JOIN forum_posts_text AS pb ON pa.post_id = pb.post_id
LEFT JOIN forum_users ON user_id = post_poster
WHERE post_topic_id = ‘224′
AND pa.post_id >= (
SELECT MAX( post_id )
FROM (
SELECT post_id
FROM forum_posts
WHERE post_topic_id = ‘224′
ORDER BY post_id ASC
LIMIT 6075 , 1
) AS tmp )
ORDER BY pa.post_id ASC
LIMIT 15
~ 0.09 sec versus ~ 0.004 sec
Tables with posts: 2×292,000+ rows (25MB + 89MB)
Topic: 6092 rows (rows that meet condition WHERE post_topic_id = ‘224′)
###############
Later tests:
.. LIMIT 66240 , 15
versus
..
LIMIT 66240 , 1
..
LIMIT 15
~ 0.5 sec versus ~ 0.03 sec
Tables with posts: 2×377,000+ rows (33MB + 120MB)
Topic posts: 66259 rows (rows that meet condition WHERE post_topic_id = actual id)
[...] criterion, is from the mysql handbook ,You may have a look at this article about Mysql limit:MYSQL limit Optimization | EaseMarry Blogs In the article uses limit directly, but first gains to offset id then uses limit size to gain the [...]
[...] 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/ [...]