mysql - 此简单查询的正确 MySQL 索引

Question

我有一个简单的查询，它执行分组依据和排序依据(带有连接)，但如果没有临时表和文件排序，它似乎无法工作。这是查询:

SELECT items.name, max(activity.created_at), count(items.item_id) as total_count
FROM activity
INNER JOIN items ON activity.item_id = items.item_id 
WHERE activity.user_id = 'XXX'  
GROUP BY activity.item_id 
order by activity.created_at desc
LIMIT 0, 15

我有一个索引:

(activity.item_id, activity.user_id, activity.created_at) - idx_item_created 
(activity.user_id, activity.created_at) - user_id_2

当我强制 idx_item_created 时，它会进行全表扫描，默认情况下它使用 user_id_2 运行并始终生成:

Using where; Using temporary; Using filesort

有什么办法可以更好地优化这个查询吗？

Answer 1

您必须对此进行文件排序，因为您有 group by 和 order by 以及两个不同的表达式。对于此查询:

SELECT items.name, max(activity.created_at), count(items.item_id) as total_count
FROM activity INNER JOIN
     items
     ON activity.item_id = items.item_id 
WHERE activity.user_id = 'XXX'  
GROUP BY activity.item_id 
order by activity.created_at desc
LIMIT 0, 15

最好的索引是activity的覆盖索引:activity(user_id, item_id,created_at)。不过，这不会消除文件排序。