I have a phpbb board and I like to run a script to compile some stats on user's posts and other stuff. I've managed to get the script(s) working quite quickly but there is one function which can take around 100 seconds to complete which could be due to a large number of db queries (that I don't think are that efficient).
I run two loops. The first gathers topic data and puts it into the $topic array (specifically, the user id of the person who started the topic so I can get it quickly from within the post loop).
$top_rsult=mysql_query("SELECT `topic_id`,`topic_poster`,`topic_time` FROM `topics` ORDER BY `topic_time` ASC");
$t=0;
while ($t<mysql_numrows($top_rsult)) {
$id = mysql_result($top_rsult,$t,'topic_id');
$topicposter[$id] = mysql_result($top_rsult,$t,'topic_poster');
++$total_newt[$topicposter[$id]];
++$t; }
Basically I'm trying to get the following information for each user:
- Total number of posts
- Of total posts, how many are: new topics, replies to own topics, replies to other user's topics
- Total number of replies this user's topics have received (not including their own replies)
$pst_rsult=mysql_query("SELECT `post_id`,`poster_id`,`post_time`,`topic_id` FROM `posts` WHERE `post_time` >= '" . $earliest_to_consider . "' ORDER BY `post_time` ASC");
$p=0;
while ($p<mysql_numrows($pst_rsult)) {
$id = mysql_result($pst_rsult,$p,'post_id');
$pst[$id][0] = $id;
$pst[$id][1] = mysql_result($pst_rsult,$p,'poster_id');
$pst[$id][2] = mysql_result($pst_rsult,$p,'post_time');
$pst[$id][3] = mysql_result($pst_rsult,$p,'topic_id');
//Add 1 to total post count for this user
++$total_msgs[$pst[$id][1]];
if ($topicposter[$pst[$id][3]]==$pst[$id][1]) {
//Add 1 to user for reply to own topic
++$total_rown[$pst[$id][1]];
} else {
//Add 1 to show topic poster has received a reply
++$total_rply[$topicposter[$pst[$id][3]]];
}
++$p; }
The above code gives:
$total_msgs[userid] = total posts by this user.
$total_newt[userid] = number of new topics
$total_rown[userid] = total which are replies to own topics (this also includes posts which are new topics, not replies but i can get the true value by -newt from this).
from these, i can calculate the number of posts which were replies to other users.
$total_rply[userid] = total replies received to topics by this user
It all works properly (i think) as I've checked it but it is quite slow.
The code shown above is simplified. When I actually run the script, I calculate three values for each of the above: 1 for the last 25 days, 1 for the last month (1st to 31st) and 1 for the previous month, stored like this:
$total_msgs[userid][timeperiod] where timeperiod = 0,1 or 2 depending on which i'm calculating. This doesn't increase the number of db queries, I just add a "if posttime>xx and posttime<xx then ++" for each timeperiod.
Any ideas or suggestions on how to improve speed?
Thank you very much!
