问题描述
我有一个简单的查询来分组需要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()组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!