title: mysql count group by order by optimization
body: <p>I have a table to store <code>tag name</code> for <code>posts</code></p>
  
  <pre><code>table: tagname
  tags                    |  pid
  festival                | 10034
  New York Fashion Week   | 10034
  festival                | 10035
  car                     | 10036
  ...
  </code></pre>
  
  <p>The table now has already 590,000 records. Now I want to get the first 10 most popular tags from this table.</p>
  
  <pre><code>SELECT tags, COUNT(*) as Num FROM tagname
  GROUP BY tags
  ORDER BY Num DESC
  </code></pre>
  
  <p>This will cost 23.88 seconds. return <code>358 kinds of tags</code></p>
  
  <pre><code>tags       |  Num
  festival   |  7201
  art        |  6988
  gift       |  6755
  ...
  </code></pre>
  
  <p>How to optimization this query, even in <code>my.cnf</code>? I tried to add index for tags, it seems with no effect. </p>
  
  <p>EDIT: 
  <code>EXPLAIN SELECT tags, COUNT(tags) as Num  FROM</code>tagname<code>GROUP BY tags  order by Num DESC</code></p>
  
  <pre><code>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
  </code></pre>