Page 1 of 2

How should I determine # of posts

Posted: Sat Dec 03, 2005 5:16 pm
by seodevhead
I am building something very similar to a message forums like this, where users post many times. I want to make visible each user's "# of posts:" in each post they make. Here is my question,

Should I create a column in the 'users' table called 'num_posts' that increments with each post made by the user?

:: OR ::

Should I just run a quick query like...

Code: Select all

"SELECT COUNT(*) FROM posts WHERE user_id=$uid"

within the loop that prints out all the posts/threads. I am afraid this method of calculating the number of posts is processing intensive... but I dunno. What would you suggest for this? Thanks for your help! Take care.

Posted: Sat Dec 03, 2005 5:28 pm
by Deemo
you could use mysql_num_rows() with that query

Posted: Sat Dec 03, 2005 5:43 pm
by John Cartwright
Deemo wrote:you could use mysql_num_rows() with that query
Actually you would run that query through mysql_fetch_assoc or its sister functions... count() already returns the affected rows.

Posted: Sat Dec 03, 2005 5:58 pm
by seodevhead
Well I understand how to do all that stuff, I just was looking for a recommendation as to which method to use to count the # of posts...

By a counting query like 'SELECT COUNT(*) FROM posts WHERE user_id=$uid'

or

by having an incrementing 'num_posts' field in the 'users' table that would only require..
'SELECT num_posts FROM users WHERE user_id=$uid'

I guess what I am really asking is whether or not the counting query is too taxing and would slow things down. Thanks for your help!

Posted: Sat Dec 03, 2005 6:00 pm
by foobar
If you want your forum to be scalable, use the second option. This is what phpbb uses, and most other forum systems that I've come across. As long as you perform all postings and deletions within the forum system, you should be fine. This fails when someone decides to screw around with the database manually, which they probably shouldn't be doing anyway.

Posted: Sat Dec 03, 2005 6:32 pm
by seodevhead
When you say 'second option', do you mean the counting query or the num_posts column??? Thanks!

Posted: Sat Dec 03, 2005 6:45 pm
by John Cartwright
It seriously comes down to your preference. phpBB dynamically calculates the posts via count() while other forum softwares such as invisionboard store the post count in the database.

count() is extremely fast, so I would probably go that route, and in doing so you avoid having to query increment the post down up or down when posting or deleting posts

Posted: Sat Dec 03, 2005 6:46 pm
by foobar
seodevhead wrote:When you say 'second option', do you mean the counting query or the num_posts column??? Thanks!
By second option, I meant having a post_count column in the user table.

Posted: Sat Dec 03, 2005 7:20 pm
by neophyte
Jcart wrote:It seriously comes down to your preference. phpBB dynamically calculates the posts via count() while other forum softwares such as invisionboard store the post count in the database.

count() is extremely fast, so I would probably go that route, and in doing so you avoid having to query increment the post down up or down when posting or deleting posts
What JCart says is true. I would only add that if activity approaches high levels of traffic doing count() will eat a lot of system resources. I read a thread on the phpBB board that talked about what someone had to do when traffic on the board reached 10000 or so... One of the things recommended was to get rid of totaling the posts with a query on all posts... Storing it in a field would be more efficient on system resources. IMHO...

Posted: Sat Dec 03, 2005 7:23 pm
by foobar
Jcart wrote:[...] phpBB dynamically calculates the posts via count() [...]
Nope. It saves 'em in the database. It's in `<table prefix>_users` --> `user_posts`.

Posted: Sat Dec 03, 2005 7:26 pm
by neophyte
:oops: :roll: :oops:

Posted: Sat Dec 03, 2005 7:28 pm
by John Cartwright
foobar wrote:
Jcart wrote:[...] phpBB dynamically calculates the posts via count() [...]
Nope. It saves 'em in the database. It's in `<table prefix>_users` --> `user_posts`.
Could have sworn it was different .. :?

Posted: Sat Dec 03, 2005 7:29 pm
by foobar
neophyte wrote::oops: :roll: :oops:
Jcart wrote:
foobar wrote:
Jcart wrote:[...] phpBB dynamically calculates the posts via count() [...]
Nope. It saves 'em in the database. It's in `<table prefix>_users` --> `user_posts`.
Could have sworn it was different .. :?
One word: pwnd! :)

It really is that way! I have phpbb2 on my machine here, opened up the MySQLCC and checked it. And, whaddya know...

Posted: Sat Dec 03, 2005 7:31 pm
by seodevhead
Well with this being said, is it still efficient to use COUNT() for my purposes? Ideally I would love to use count() just because it is so easy and will always return the right answer. Thanks for the good input guys.

Posted: Sat Dec 03, 2005 7:35 pm
by John Cartwright
It will basically come down to this, count() will be initially faster but as your database grows it will eventually become slower than using a seperate column storing post count. I'm going to run a quick benchmark and will get back to you soon.