How should I determine # of posts

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

How should I determine # of posts

Post 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.
Deemo
Forum Contributor
Posts: 418
Joined: Sun Jan 18, 2004 11:48 am
Location: Washington DC

Post by Deemo »

you could use mysql_num_rows() with that query
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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!
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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.
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

When you say 'second option', do you mean the counting query or the num_posts column??? Thanks!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post 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...
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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`.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

:oops: :roll: :oops:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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 .. :?
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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...
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
Post Reply