views:

99

answers:

2

Hi, I was wondering if it which is faster when trying to, for example, check how many posts are in a particular thread on a forum. Should I...

(a) Go through each post in the database with the particular thread ID, and count how many rows

or

(b) Add one to a separate column in the threads database every time a thread is made, and then query that single row

Thanks.

+3  A: 

Premature optimization is the root of all evil.

Use solution (a) to start, and then, IF your site needs it, switch to solution (b).

Ben Alpert
+9  A: 

What's wrong with having an index for the thread ID? Wouldn't a simple COUNT expression grouped by the thread ID field suffice?

With any caching at all, this would be plenty fast from what I can tell.

--This will provide counts for all threads
SELECT COUNT(threadID)
FROM Posts
GROUP BY threadID;


--This will provide count for one thread
SELECT COUNT(threadID)
FROM Posts
WHERE threadID=123
GROUP BY threadID;
Joe Philllips
+1 COUNT/GROUP BY query (plus index on thread ID) is the right solution for this.
cletus
SELECT count(*) FROM tableName GROUP BY threadID just in-case.
Unkwntech
This is the standard way to optimize. If for some reason this was slow, the next option is to add the column to the thread table and have a "post count" there that is updated with each new post.
Sam