优化 DISTINCT / LIMIT MySQL 查询分页
我有以下 SQL:
SELECT group_id FROM products WHERE category = 12345 GROUP BY group_id LIMIT 0, 10
或:
SELECT DISTINCT group_id FROM products WHERE category = 12345 LIMIT 0, 10
LIMIT 用于分页。我想知道如何避免 MySQL 查找所有行，直到找到我想要的 10 个不同的 group_id。例如，如果我这样做：
SELECT DISTINCT group_id FROM products WHERE category = 12345 LIMIT 200, 10
它将扫描类别 = 12345 的前 200 行，然后开始收集 10 个不同的 group_ids？
更新：如果我在 (group_id, Category) 上创建索引会怎样？
I have the following SQL:
SELECT group_id FROM products WHERE category = 12345 GROUP BY group_id LIMIT 0, 10
or:
SELECT DISTINCT group_id FROM products WHERE category = 12345 LIMIT 0, 10
The LIMIT is used for pagination. I want to know how can I avoid MySQL to lookup all rows until it finds the 10 distinct group_ids that I want. For example if I did:
SELECT DISTINCT group_id FROM products WHERE category = 12345 LIMIT 200, 10
It will scan the first 200 rows with category = 12345, than start collecting then 10 distinct group_ids?
UPDATE: What if I created an index on (group_id, category) ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的查询将找到前 200 个不同的行，然后输出接下来的 10 个。
DISTINCT在LIMIT之前完成。根据索引和表的存储方式，一旦满足限制子句，它就可以停止收集唯一值。如果您想要区分的值已建立索引，则最有可能出现这种情况。（顺便说一句，如果您使用
LIMIT 10 OFFSET 200而不是旧的不直观的LIMIT 200, 10表示法，您将提高查询的可读性。）Your query will find the first 200 distinct rows, then output the next 10. The
DISTINCTis done before theLIMIT. Depending on indexing and how the table is stored, it can stop collecting unique values once it fulfills the limit clause. This is most likely if the value you want distinct is indexed.(BTW, you will improve readability of the query if you use
LIMIT 10 OFFSET 200instead of the older unintuitiveLIMIT 200, 10notation.)