The index-page of my forum looks like this:
| Forum | Topics | Answers |
----------------------------
| Forum A | 123 | 45678 |
| Forum B | 345 | 23128 |
| Forum C | 567 | 2328 |
Here's my SQL code that works so far but I think there must be a better solution:
SELECT f.`id`, f.`name`, f.`description`, f.`type`,
(SELECT COUNT(`id`)
FROM threads
WHERE `forum_id` = f.`id`) AS num_threads,
(SELECT COUNT(p.`id`)
FROM threads t, posts p
WHERE p.thread_id = t.id
AND t.forum_id = f.id) AS num_posts
FROM `forums` f ORDER BY `position`
How would you speed up this query? Any alternatives to subqueries?
Thanks in advance!
Something like this, joining the basic select with a couple of subqueries with group by clauses (so they are executed once each, rather than once each per row)
SELECT f.id, f.name, f.description, f.type, tc.RowCnt, ta.RowCnt
FROM `forums` f
INNER JOIN (SELECT forum_id, COUNT(id) AS RowCnt FROM threads GROUP BY forum_id) tc
INNER JOIN (SELECT forum_id, COUNT(p.id) AS RowCnt FROM threads t INNER JOIN posts p ON p.thread_id = t.id GROUP BY forum_id) ta
ORDER BY position
You could likely improve this by doing one of the counts in the main select rather than the subselect (but it is late friday and I am tired!).