Hi,
I have a system where members get scores, or "points". I have a chart that displays the most points earned per week, per month,..and alltime. I get the feeling that my current method is laughably bad. Here's a simplified version of what I am doing:
A user gets points and the script enters the userid, date, and amount of points into the database. I am putting all entries in seperately so that I have the ability to see which points are a within the time range.
I then run a CRON every night like so:
//delete all entries more than 30 days old
$sql9 = "DELETE FROM monthly_score WHERE score_date < date_sub(NOW(), interval 30 day)";
//Set all monthly scores to 0 before running the update:
$sql7 = "UPDATE songs SET monthly_score = 0, weekly_score = 0";
//tally up the remaining entries:
$sql2 = "SELECT userid FROM monthly_score GROUP BY userid";
while($row2= mysql_fetch_array($result2)){
$userid = $row2[userid ];
$sql5 = "SELECT SUM(score) FROM monthly_score WHERE userid ='$userid ";
$score = mysql_result($result3, 0);
//follow around again for weekly scores:
$sql5 = "SELECT SUM(score) FROM monthly_score WHERE date > date_sub(NOW(), interval 7 day) AND userid ='$userid ";
$result3 = mysql_query($sql5) or die(mysql_error());
$week_score = mysql_result($result3, 0);
//and then update the member's table to reflect the new points:
$sql7 = "UPDATE table SET monthly_score = '$score', weekly_score = '$week_score ' WHERE songid = '$songid'";
The above code is working, but the size of the database and length of queries is getting out of hand. I've been considering a few things and am hoping to hear some advice.
--Have one insert per day that stores the date, and then add points to that record instead of inputting new ones. I guess this would require an extra query for every point to check and see if there is already a record available to update and if not,.insert.
--Some sort of system that doesn't actually use the date but just counts to 30? Like the first day the script is ran the "date" is 1. That number would be incremented by one with a daily cron. On day 30 it would tell it to DELETE WHERE day =1, On the next day it would be coded to DELETE WHERE day=30,..etc
I'm sure there are better solutions but as you can see,..I'm grasping at straws here. 🙂
Thanks for any advice.
Oh,..and while on the subject,,..the current program is deleting a lot of data every day and giving my db some overhead. Would you advise following that script up with an OPTIMIZE?
