post count set to 0. is this PHP fault?

Question

hello,

i have found lots of website running PHP which the post count from some hundreds back to 0.

for example, a user with some hundreds of post count posting a forum topic. the loads is very slow ... and when it posted successfully, that user post count become 0.

i know this is because not enough memory for PHP.

all my website running PHP. when use low memory for PHP, usually the description and lots of others of the post will become blank. i think i have no problem with mysql. i give mysql with lots of memory and lots of optimization.

is this problem only happen in PHP?

(correct me if im wrong)

Answer 1

So posting for a user with lots of posts is slow. Does it go fast for a user with few or no posts at all? How are you determining the post count? Are you using something like:

 $sql = "SELECT count(*) FROM posts WHERE userID = $someID";
 $res = mysql_query($sql) or die("Mysql error: " . mysql_error());
 $post_count = mysql_fetch($res, 0, 0);

or something like:

 $sql = "SELECT * FROM posts WHERE userID = $someID";
 $res = mysql_query($res) or die("Mysql error: " . mysql_error());
 $post_count = 0;
 while($row = mysql_fetch_assoc($res)) {
     $post_count++;
 }

The first one would run MUCH faster overall, as you're only retrieving a single number. Assuming the 'userID' field is properly indexed, there's no reason such a construct would take more than a second or two to run, worst case, on any reasonable data set.

The second one, however, will run MUCH slower. It fetches ALL the data from each matching record, whether it's needed or not. It then throws away that data, and just increments a counter. If there's a blob field in that table, you're also retrieving the blob, which could very well exceed the available memory.

As well, are you doing proper error checking in your script? Never assume that a query will run successfully. Even if the SQL is correct, there's many other reasons for the query to fail. Always check mysql_error() and its various cousins after every database call.

Beyond that, you haven't given us anything to work with. These are just some general tips. Maybe put up a sample of the posting code that runs slowly.