Order recordsets by table linked record count

[asantos]

I have 2 tables:
* forum (id_forum, title)
* forum_msg (id_forum_msg, id_forum, msg)

I need to list the TOP 5 commented forums. This is my current query:
SELECT forum.id_forum,COUNT(*) AS q FROM forum_msg INNER JOIN forum ON forum_msg.id_forum=forum.id_forum GROUP BY forum.id_forum

Any ideas on how to improve it? Thanks!

[Demaestro]

If you order by the count field, descending, and limit the results to 5, then the sql will only return the ones you want and you don't have to find the top 5 from within that result set. And it might help to speed things up if you only count from one table

SELECT forum.id_forum,COUNT(forum_msg.*) AS q FROM forum_msg INNER JOIN forum ON forum_msg.id_forum=forum.id_forum GROUP BY forum.id_forum order by q desc limit 5

I think I am counting the right table.

[edited by: Demaestro at 8:19 pm (utc) on May 14, 2009]

[asantos]

thanks, worked great!

[Demaestro]

sweet!