当您分页时,按字母顺序分页会逐渐变慢(MySQL)
我有一个超过 100k 行的数据集,所以它不是很小,但也不是很大。翻阅结果时,当您转到较高的页面时,速度会逐渐变慢。换句话说,这个查询:
SELECT * FROM items WHERE public = 1 ORDER BY name LIMIT 0,10
执行速度比我在 name 上有索引要快得多
SELECT * FROM items WHERE public = 1 ORDER BY name LIMIT 10000,10
,我曾经在 public 上有一个索引,但我删除了它,因为它似乎进一步降低性能。
这里有什么想法吗?有没有一种简单的方法可以加快速度?我正在考虑取消查看较高页面的功能,因为除了机器人之外,没有人真正浏览过第 2 或第 3 页,而且他们有更简单的方法来查找该内容。
I've got a dataset with over 100k rows, so it's not tiny, but not huge either. When paging through the results, it gets progressively slower as you go to higher pages. In other words, this query:
SELECT * FROM items WHERE public = 1 ORDER BY name LIMIT 0,10
executes much faster than
SELECT * FROM items WHERE public = 1 ORDER BY name LIMIT 10000,10
I have an index on name, and I used to have an index on public, but I removed it since it seemed to degrade performance even more.
Any ideas here? Is there an easy way to speed this up? I'm considering removing the ability to view the higher pages since nobody really browses past page 2 or 3, except robots, and there are easier ways for them to find that content.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
大 LIMIT 问题:
资源:
The large LIMIT problem :
Resources :