mysql slow COUNT()

Question

Hi all,

I'm having some difficulties with a query:

SELECT d.*, 
       (SELECT COUNT(id) FROM downloads WHERE category = d.category) AS count 
FROM downloads d 
GROUP BY d.category 
ORDER BY count DESC

So, I'm trying to get the total downloadcount of each category but this query hangs each time I run it.

The downloads table has +- 20000 rows

What am I doing wrong?

Answer 1

SELECT category, count(id) as count
FROM downloads d 
GROUP BY d.category 
ORDER BY count DESC

Answer 2

I think you'd be better off doing this:

  SELECT category, count(*) as cnt
    FROM downloads
GROUP BY category
ORDER BY cnt desc;