删除“使用文件排序”来自查询
我有以下查询：
SELECT *
FROM shop_user_member_spots
WHERE delete_flag = 0
ORDER BY date_spotted desc
LIMIT 10
运行时，需要几分钟。该表大约有 250 万行。
这是表格（不是我设计的，但我可以进行一些更改）：
最后，这里是索引（再说一次，不是我做的！）： 
我一直在尝试让这个查询快速运行几个小时，但没有成功。
这是 EXPLAIN 的输出： 
非常感谢任何帮助/文章。
I have the following query:
SELECT *
FROM shop_user_member_spots
WHERE delete_flag = 0
ORDER BY date_spotted desc
LIMIT 10
When run, it takes a few minutes. The table is around 2.5 million rows.
Here is the table (not designed by me but I am able to make some changes):
And finally, here are the indexes (again, not made by me!):
I've been attempting to get this query running fast for hours now, to no avail.
Here is the output of EXPLAIN:
Any help / articles are much appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据您的查询，您想要的索引似乎位于
(delete_flag, date_spotted)上。您的索引包含两列，但id列位于它们之间，这会使索引对于基于date_spotted的排序没有帮助。现在mysql是否会使用基于Zohaib答案的索引我不能说（抱歉，我最常使用SQL Server）。Based on your query, it seems the index you would want would be on
(delete_flag, date_spotted). You have an index that has the two columns, but theidcolumn is in between them, which would make the index unhelpful in sorting based ondate_spotted. Now whether mysql will use the index based on Zohaib's answer I can't say (sorry, I work most often with SQL Server).我在解释计划中看到的问题是，发现日期上的索引没有被使用，正在使用插入的文件排序机制来排序（就删除标志上的索引而言，如果列正在创建的索引包含唯一值）
mysql 文档说
索引不会用于 order by 子句 if
http://dev.mysql.com/doc/ refman/5.0/en/order-by-optimization.html
我想这里的情况也是如此。虽然您可以尝试使用 Force Index
The problem that I see in the explain plan is that the index on spotted date is not being used, insted filesort mechanism is being used to sort (as far as index on delete flag is concerned, we actually gain performance benefit of index if the column on which index is being created contains unique values)
the mysql documentation says
Index will not used for order by clause if
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
I guess same is the case here. Although you can try using Force Index