Mysql加速max（）组 [英] Mysql speed up max() group by

问题描述

我有一个简单的查询来分组需要0.0045秒的行。 for 300.000 rows

SELECT cid FROM表GROUP BY cid

$ b

SELECT MAX（id）id，cid FROM表GROUP BY cid

如何加快查询速度？查询在我的本地主机上运行以进行测试。 id = primary key，并且我有cid上的索引。

解决方案

原因是两个查询之间的差异：

• 您的第一个查询永远不会触及表 - 它只依赖于索引
  
• 您的第二个查询实际需要击中所有行

因此，要回到更理想的第一种情况，您需要一个索引，它可以同时提供： cid和最小/最大ID。您可以尝试通过在（cid，id）上创建索引来实现此目的。

I have an easy query for grouping rows which takes 0.0045 sec. for 300.000 rows

SELECT cid FROM table GROUP BY cid

When I add MAX() to query it takes 0.65 sec to return.

SELECT MAX(id) id, cid FROM table GROUP BY cid

How can I speed up this query? The query runs on my local host for testing. id = primary key and I have index on cid.

解决方案

The reason is the difference between the two queries:

• Your first query will never touch the table - it wil rely on the index only
• Your second query actually needs to hit all rows

So to get back to the more optimal first case, you need an index, that can provide both: grouping by cid and min/maxing id. You could try to achieve this by creating an index on (cid,id)

这篇关于Mysql加速max（）组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！