Ok im currently writting a forum, for my site in mysql, and php

i have made a user profile section, and a private messaging system, and that all works perfectly.

now there is something i would like to know before i continue.

on each forum, on these forums, you will notice it says how many topics and posts there are within that forum.

Now, this is how im thinking of adding, and deducting to that number

i am going to store the number of topics and posts in a table called forums

along with the forum_id, forum_name etc etc

now, the method im thinking of using is this one

everytime a user creates a topic, the number is retrieved from the database, 1 is added to it, and then the new number (old_number + 1) is then saved back to the table

the same method is used when a user creates a post. and the reverse happens when a user deletes a topic, or post

now, if a topic is moved, the number topic number is deducted one from the forum its going from, and added by one, to the forum its going to, and the same for the number of posts in that topic being moved.

now, that method will work. But i want to know something. Will it remain accurate if loads of people are concurrently posting???

say the maths that does it is perfect, and there is no problem with the script, it does what its mean to do. will it stay accurate in this case???

or can it still become inaccurate even though the script is adding, deducting correctly????

Thanx

You would need to lock the table that you are updating with the number of posts to make sure that only one process at a time could affect the value. When locking tables you need to be careful that you do not create a deadlock situation. You would lock the table, retrieve the record, update, and unlock the table. You want to try not to do any other processing during the lock period so that the table is locked for the shortest amount of time possible and you would never want to have to wait on user input while a table is locked.

If everything worked perfectly the counts would always be correct. In reality they will probably get off at some point, so you might want to have a process that runs daily to recalculate the counts.

The other option is to calculate your counts dynamically each time by doing a select on the records that you want to count and only selecting one small field to try and keep the resources used small.

There are trade-offs to both methods and it's hard to say which is better.

Richard

To make it remain accurate do not retreive from database, add a
number to it, then save result to the database. That correctly can cause incorrect calculation (Potentially two php-executing-threads can grab the same number, add 1 to it and save it as +1 - while it should have been +2). One alternative way of doing this is to do the addition IN THE DATABASE- in the SQL!

Ie.

UPDATE forums SET topics=topics+1 WHERE forumid= $fid

Good point beowulfdk. I sometimes forget some of the nicer features of a relational database. I do day-to-day programming on a network database and it's missing a lot of those features.

Richard

thats what i was worrying about

2 threads getting the same number into a var then saving the new number


so if i do it in the actualy sql code, it will remain accurate?

thanx

>so if i do it in the actualy sql code, it will remain accurate?
Yes, to my knowledge. I think a single sql update is thread-safe.

If anyone with more mysql experience disagrees please say so :-)

Noldar, I like your signature :)

yes doing the incrementing in the query itself will always remain accurate (unless the database goes down etc)...

of course doing it the way you say takes a lot of queries per post and if you expect heavy loads you should generally try to minimize queries, especially inserts/updates... could be done more efficiently...

yes but if i do not store this information it would mean that everytime a user goes to your forum index, the script would have to count (using mysql_num_rows()) how many topics and posts where are in each forum

surely this would cause a major performance problem if there are 80 users online, and the server has to count the number of topics and posts in each forum

You don't have to store the _number_ of topics in the DB... Just the topics themselves; to retrieve how many topics are in the DB, just do something like (assuming you only want to retrieve the data for the first forum)

SELECT COUNT(topicid) as total_topics FROM topics WHERE forumid=1;