mysql通过优化按顺序对组进行计数 [英] mysql count group by order by optimization

问题描述

我有一张表用于存储标记名称用于帖子

 表：标记名
标记| pid 
 festival | 10034 
纽约时装周| 10034 
 festival | 10035 
 car | 10036 
 ... 
  

该表现在已经有590,000条记录。

  SELECT标签，COUNT（*）作为Num FROM标记名
 GROUP BY标记
 ORDER BY NUM DESC 
  

这将花费23.88秒。返回 358种标签

  tags | Num 
 festival | 7201 
 art | 6988 
礼物| 6755 
 ... 
  

如何优化此查询，即使在的my.cnf ？我试图为标签添加索引，似乎没有效果。



编辑：
EXPLAIN SELECT标记，COUNT（标记）作为Num FROM 标记名 GROUP BY标记order by Num DESC

  id select_type表类型possible_keys key key_len ref rows额外
 1 SIMPLE标记名ALL NULL NULL NULL NULL 597649使用临时;使用filesort 
  




解决方案

将索引添加到 tags 列：

  ALTER TABLE`tagname` 
 ADD INDEX`t​​ags_index` （`tags` ASC）; 
  

编辑：

尝试创建第二个index

  CREATE INDEX tags_pid_index ON tagname（tags，pid）; 
  

然后将您的查询修改为：

  SELECT标记，COUNT（pid）为数字
 FROM标记名
 GROUP BY标记
 ORDER BY数字DESC 
  

I have a table to store tag name for posts

table: tagname
tags                    |  pid
festival                | 10034
New York Fashion Week   | 10034
festival                | 10035
car                     | 10036
...

The table now has already 590,000 records. Now I want to get the first 10 most popular tags from this table.

SELECT tags, COUNT(*) as Num FROM tagname
GROUP BY tags
ORDER BY Num DESC

This will cost 23.88 seconds. return 358 kinds of tags

tags       |  Num
festival   |  7201
art        |  6988
gift       |  6755
...

How to optimization this query, even in my.cnf? I tried to add index for tags, it seems with no effect.

EDIT: EXPLAIN SELECT tags, COUNT(tags) as Num FROMtagnameGROUP BY tags order by Num DESC

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE      tagname ALL     NULL    NULL     NULL    NULL   597649  Using temporary; Using filesort

解决方案

Add an index to your tags column:

ALTER TABLE `tagname` 
ADD INDEX `tags_index` (`tags` ASC) ;

EDIT:

Try creating a second index

CREATE INDEX tags_pid_index ON tagname (tags, pid);

Then modify your query to:

SELECT tags, COUNT(pid) as Num 
FROM tagname
GROUP BY tags
ORDER BY Num DESC

这篇关于mysql通过优化按顺序对组进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！