Count posts with php or store in database

I have these tables:

forum

• id
• name
• description

posts

• id
• forumID
• body

What I wonder is should I create a field in forum where i store the number of posts that been made in that forum? Update it every time someone makes a post. or should I count them with php? Is it a huge performance difference?

Small forum - not a big deal. But as your forum grows it will make a huge difference counting the number of posts in a simple query. The difference will be even more apparent if you store the posts in an innodb table instead of a myisam. So if you're not worried about it growing too big, use a query. Otherwise, add a column.

Edit - my advice? Save yourself the future headache and add a column.

You should just query your posts table and count the posts with the forumID you want.

SELECT count(*) FROM `posts` WHERE `forumID` = XX;

You should have an index on forumID anyway, since you will use it often in your queries. With that index, this query will run very, very quickly.

I guess it's better using MySQL's count(), I don't think there's really a great difference in performance.

The following query should be fast:

SELECT COUNT(*)
FROM posts
WHERE forumID = @forumID

You should make sure that there is an index on the forumID column so that it doesn't need to do a table scan.

To the questions "should I add a [counter] to my forum table" and "is there a huge performance difference" the answers are "yes" and "yes, if you have lots of messages." Even if you don't have tons of messages, I'd still recommend adding such a counter to your forum table.

Also, you should consider using a forum software instead of coding your own, because you'll encounter lots of that kind of dilemmas and you'll spend quite a lot of time rewriting stuff that already exists.

All in one shot:

SELECT post.forumID, forum.name, COUNT(post.forumId) as postCount
FROM post INNER JOIN forum ON post.forumId = forum.id 
GROUP BY post.forumID, forum.name

I'd create a cron script that calculates the COUNT(*) each hour or whenever an old post is deleted:

SET @mytime = CURRENT_TIME()

UPDATE  forums f
SET     post_count = 
        (
        SELECT  COUNT(*)
        FROM    posts p
        WHERE   p.forumID = f.id
                AND p.date <= @mytime
        ),
        count_updated = @mytime

, and COUNT(*) the new posts in a query:

SELECT  post_count +
        (
        SELECT  COUNT(*)
        FROM    posts p
        WHERE   forumID = f.id
                AND p.date > f.count_updated
        )
FROM    forums f
WHERE   f.id = @id

This is a good tradeoff between UPDATE overhead and SELECT overhead.