如何避免此 mysql 查询的文件排序
我需要一些帮助来避免此查询的文件排序。
SELECT id
FROM articles USE INDEX(group)
WHERE type = '4'
AND category = '161'
AND did < '10016'
AND id < '9869788'
ORDER BY id DESC
LIMIT 10
INDEX(group) 是 (type, category, did, id) 的覆盖索引，
因为 ORDER BY id DESC，执行文件排序。有没有办法避免此类查询的文件排序？
I need some help in avoiding filesort for this query.
SELECT id
FROM articles USE INDEX(group)
WHERE type = '4'
AND category = '161'
AND did < '10016'
AND id < '9869788'
ORDER BY id DESC
LIMIT 10
INDEX(group) is a covering index of (type, category, did, id)
Because of ORDER BY id DESC, filesort is performed. Is there a way to avoid filesort for such query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
更改索引列顺序。该索引对于排序来说是无用的，因为它是第四列，并且还没有准备好按原样使用。
当然，这会影响该 WHERE 索引的有用性，因为
在 MySQL 文档，你打破了“你在键的非连续部分使用 ORDER BY”和“用于获取行的键与使用的键不同”在订单中BY”
编辑：根据我上面的链接，您不能拥有同时满足 WHERE 和 ORDER BY 的索引。由于我上面发布的两个条件，它们是互斥的
另一个建议：
Change the index column order. The index is useless for the sort because it'^s the 4th column and isn't ready to be used as-is.
Of course, this affects the usefulness of the index for this WHERE because you need an inequality column before an equality one
In the MySQL docs, you break "You use ORDER BY on nonconsecutive parts of a key" and "The key used to fetch the rows is not the same as the one used in the ORDER BY"
Edit: as per my link above, you can't have an index that satisfies both WHERE and ORDER BY. They are mutually exclusive because of the 2 conditions I posted above
Another suggestion:
在 id 上创建索引并输入它应该适合您。
如果 Id 是唯一的，您还可以为其创建主键。
您的查询正在使用文件排序，因为优化器可能正在使用您在查询中创建的索引的第一列。
Create index on id and type it should work for you.
If Id is unique you can also create the Primary Key for it.
your query is using file sort because it might be possible that the optimizer is using the first column of index you created in the query.