CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
cj333
cj333

Reputation: 2609

mysql count group by order by optimization

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

Upvotes: 4

Views: 2591

Answers (2)

kidz
kidz

Reputation: 308

Have you tried like this:

    SELECT tags, COUNT(*) as Num FROM tagname
    GROUP BY tags HAVING COUNT(*) > 1
    ORDER BY Num DESC LIMIT 10

The trick can be: If you know the minimum popularity number, you can change the number in COUNT(*) > x

Thanks

Upvotes: 1

juergen d
juergen d

Reputation: 204746

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

Upvotes: 2

Related Questions