转载:Why does MYSQL higher LIMIT offset slow the query down?
来源:互联网 发布:淘宝买家具哪个品牌好 编辑:程序博客网 时间:2024/05/28 15:56
来自:http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down
Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key*
So
SELECT * FROM large ORDER BY `id` LIMIT 0, 30
takes far less than
SELECT * FROM large ORDER BY `id` LIMIT 10000, 30
That only orders 30 records and same eitherway. So it's not the overhead from ORDER BY.
Now when fetching the latest 30 rows it takes around 180 seconds. How can I optimize that simple query?
It's normal that higher offsets slow the query down, since the query needs to count off the first OFFSET + LIMIT
records (and take only LIMIT
of them). The higher is this value, the longer the query runs.
The query cannot go right to OFFSET
because, first, the records can be of different length, and, second, there can be gaps from deleted records. It needs to check and count each record on its way.
Assuming that id
is a PRIMARY KEY
of a MyISAM
table, you can speed it up by using this trick:
SELECT t.*FROM ( SELECT id FROM mytable ORDER BY id LIMIT 10000, 30 ) qJOIN mytable tON t.id = q.id
See this article:
- MySQL ORDER BY / LIMIT performance: late row lookups
MySQL cannot go directly to the 10000th record (or the 80000th byte as your suggesting) because it cannot assume that it's packed/ordered like that (or that it has continuous values in 1 to 10000). Although it might be that way in actuality, MySQL cannot assume that there are no holes/gaps/deleted ids.
So, as bobs noted, MySQL will have to fetch 10000 rows (or traverse through 10000th entries of the index on id
) before finding the 30 to return.
EDIT : To illustrate my point
Note that although
SELECT * FROM large ORDER BY id LIMIT 10000, 30
would be slow(er),
SELECT * FROM large WHERE id > 10000 ORDER BY id LIMIT 30
would be fast(er), and would return the same results provided that there are no missing id
s (i.e. gaps).
参考:
1.
为什么长尾数据的翻页技术实现复杂 --文章很好
- 转载:Why does MYSQL higher LIMIT offset slow the query down?
- The Slow Query Log
- Why Does Gmail Go Down?
- mysql slow query
- MySQL Slow Query 设置
- mysql slow query
- MySQL Slow Query EXPLAIN
- Mysql limit & offset 区别
- MYSQL limit,offset 区别
- MYSQL limit,offset 区别
- MYSQL limit,offset 区别
- MYSQL limit,offset
- MYSQL limit,offset 区别
- MYSQL limit,offset 区别
- 优化mysql limit offset
- mysql offset 和 limit
- MYSQL limit,offset 区别
- mysql中limit,offset
- 树链剖分小结,模板
- Codility上的问题 (40) Phosphorus 2014
- python杂记
- 机器视觉开源处理库汇总-介绍n款计算机视觉库/人脸识别开源库/软件 -几种图像处理类库的比较-视觉相关网站
- 2014年7月7日星期一(DEMO8-7使用新的模型格式)
- 转载:Why does MYSQL higher LIMIT offset slow the query down?
- 2014年7月8日星期二(DEMO8-8加载3DSMAX ASCII格式模型.ASC)
- poj3253 Fence Repair(哈弗曼)
- win7下VC++6.0打开多个工作区间
- 2014年8月26日星期二(DEMO8-9加载COB模型)
- 机器视觉专题
- [LeetCode] Balanced Binary Tree 平衡二叉树
- 9-2加上光照计算的GOURAUD shader多边形)
- 去学隐身术吧