How to make a MySql query faster?

How to do this mysql query on 2 tables?

• I have 2 tables, one named ARTICLE and one named COMMENTS. Say ARTICLE table contains … -ID - article And COMMENT table contains… -ID -article_id (which will be related to “ ID” in the articles table) -comment I have a simple mysql query that lists all of the articles ordered by ID desc, but I also want to display the number of comments for that article… e.g if the COMMENTS table looked like this --- ID | article_id 1 .... 4 2 .... 4 3 .... 7 4 .... 4 Then the article with the ID of 4 inside the ARTICLE table has 3 comments. How would I echo out “3 comments” next to that particular article inside the same mysql query. I tried to make it clear as I could. Hope it makes sense. Thanks in advance.

• Answer:

  SELECT a.id AS "Article ID", COUNT(*) AS "Number of Comments" FROM article a JOIN comment c ON a.id = c.article_id GROUP BY a.id This assumes that either every article has at least one comment or you are not interested in the ones that don't. If otherwise, you have to do an outer join: SELECT a.id AS "Article ID", COALESCE(COUNT(*), 0) AS "Number of Comments" FROM article a LEFT OUTER JOIN comment c ON a.id = c.article_id GROUP BY a.id