3

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?

Improve this question
11
  • 2
    Post them as "normal" queries this wordpress? format is annoying. Commented Mar 6, 2014 at 22:43
  • These are CodeIgniter Active Records Commented Mar 6, 2014 at 22:44
  • 1
    Also post your indices. Commented Mar 6, 2014 at 22:45
  • Indeces and raw SQL query posted. Commented Mar 6, 2014 at 22:52
  • Have you tried INNER JOIN instead of just JOIN? Commented Mar 6, 2014 at 22:57

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Can you elaborate why this should be faster than a JOIN?
The syntax error is in the second subquery. replace WHERE forum_posts with FROM forum_posts WHERE.
@kingkero not sure about the speed. I just provided an alternative, I would suggest to Mala to test the speed. If someone has a JOIN alternative, that also should be tested.
Would it be possible to post the table structures? I think that would greatly help detect whats causing all of these queries to be slow (maybe something with keys?)
Yep, it is always best to have foreign keys as indices on the table to speed lookup. There is a discussion on if actually having them as foreign keys increase performance, but that is a different story found here: stackoverflow.com/a/507218/1935500. You should also test the INNER JOIN and see if that faster than this query now that you have your indices setup.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.