Getting the number of rows sharing the same ID in separate MySQL tables

Question

The question in a nutshell: For each row in a forums table, how can I efficiently get the number of rows of the forum_threads table and the forum_posts table that have the respective forum_id value?

I have a page showing a list of forums. For each forum, I would like to show the number of threads and the number of posts contained in that forum, and the time of the most recent post. I store forum data in three tables:

• forums which contains the forum ID, forum name, etc. Indeces are on forum_id (primary) and ordering
• forum_threads which contains the thread ID, forum ID, thread title, etc. Indeces are on thread_id (primary), forum_id
• forum_posts which contains the post ID, thread ID, forum ID, message content, timestamp, etc. Indeces are on post_id (primary), thread_id and user_id.

My (inefficient) code for generating the information I require goes as follows:

- select all rows in the `forums` table
- foreach row:
    - perform a count(thread_id) query in the `forum_threads` table matching this `forum_id`
    - perform a count(post_id) query in the `forum_posts` table matching this `forum_id` 

This is starting to take far too long to compute, and so I am trying to optimize it. I figured let's start with just the num_threads and num_posts bit within a single query:

SELECT
    `forums`.`forum_id`,
    `name`,
    `description`,
    count(forum_threads.thread_id) as num_threads,
    count(forum_posts.post_id) as num_posts
FROM
    (`forums`)
JOIN
    `forum_threads` ON `forums`.`forum_id`=`forum_threads`.`forum_id`
JOIN
    `forum_posts` ON `forums`.`forum_id`=`forum_threads`.`forum_id`
WHERE `hidden` = 0
ORDER BY `ordering`

However, this query fails because there are simply too many posts for the second join to handle. It spins at 100% cpu for a bit, and then fails with error 126 (which I gather typically results from overloading resources). In any case, it succeeds if I comment out that line, and fails when I include it.

What is the efficient way to get the number of rows of two separate tables that share an ID with the row in a "parent" table, without performing multiple separate queries for each result in a previous query?

Answer 1

Use sub queries:

SELECT
forums.forum_id, forums.name, forums.description,
(SELECT count(forum_threads.thread_id) FROM forum_threads WHERE forum_threads.forum_id = forums.forum_id)  as num_threads,
(SELECT count(forum_posts.post_id) FROM forum_posts WHERE forum_posts.forum_id = forums.forum_id)  as num_posts
FROM forums
WHERE hidden = 0
ORDER BY ordering