本文介绍了Mysql加速max()组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

10-20 04:13