Search code examples
phpmysqlperformanceforumsubquery

MySQL/PHP: Using multiple sub-queries in a query selecing multiple results, is it a bad idea?

Sorry if the title is a little... Crappy. Basically I'm writing a small forum and using multiple sub-queries to select the number of threads, number of posts, and the date of the last post in a forum while grabbing the forum's information at the same time to display on the main page!

This is my query, since I suck at explaining things:

SELECT `f`.*,
    (SELECT COUNT(`id`)
    FROM `forum_threads` 
    WHERE `forumId1` = `f`.`id1`
        AND `forumId2` = `f`.`id2`) AS `threadCount`,
    (SELECT COUNT(`id`)
    FROM `forum_posts` 
    WHERE `forumId1` = `f`.`id1`
        AND `forumId2` = `f`.`id2`) AS `postCount`,
    (SELECT `date`
    FROM `forum_posts` 
    WHERE `forumId1` = `f`.`id1` 
        AND `forumId2` = `f`.`id2` 
        ORDER BY `date` DESC LIMIT 1) AS `lastPostDate`
FROM `forum_forums` AS `f`
ORDER BY `f`.`position` ASC, `f`.`id1` ASC;

And am using the general foreach loop to display the results:

foreach($forums AS $forum) {
    echo $forum->name .'<br />';
    echo $forum->threadCount .'<br />';
    echo $forum->postCount .'<br />';
    echo $forum->lastPostDate .'<br />';
}

(Not exactly like that of course, but for the sake of explaining...)

Now I was wondering if that would be "bad" for performance, or if there was any better way of doing it? Assuming there are quite a few posts and threads in each forum.

I was originally storing "posts", "threads", and "lastPost" columns in the forum table itself, and was going to increment (posts = posts + 1) the values every time someone created a new thread or post. Though I had this idea as well and was wondering if it was any good. :P

Solution

  • I would do things a bit differently:

    It seems to me that all these three fields: threadCount, postCount and lastPostDate are fields that you can maintain on a separate table, say forum_stats which will hold only 4 columns:
    * forum_id
    * thread_count
    * post_count
    * last_post_date

    These columns can be updated via. trigger upon insert/update.
    If you'll pay this small overhead during the update operations - you'll get a very fast query for the select (and it will remain very fast regardless the number of forums/posts/threads you have).

    Another approach (not us good TMO):
    Create the stats table and run a daily (or every few hours) a batch-job which will update the stats. The price is that the data you display will never be up-to-date, and the job might require resources, you might want to run the job only at night, for example, since it's heavy and you don't want it to effect the majority of your website visitors.