Tracking monthly and weekly stats

[Dolemite50]

Hi,

I have a site where members can host mp3's. I am trying to track total play stats for each song in monthly, weekly, and all-time form.
My current setup is working but extremely slow. Basically what I have is one table for monthly stats, one for weekly, and the song table itself has a column for all-time, weekly and monthly stats.
When a member clicks play on a song,..it inserts the songid, bandid, and playdate (now()) into the monthly and weekly charts. It then adds one to the alltime play column in the song table.

I have a script that I run every night to update the total plays that looks like this. (monthly code here)

$sql9 = "DELETE FROM monthly_plays WHERE playdate < date_sub(NOW(), interval 30 day)";
		$result9 = mysql_query($sql9) or die(mysql_error());

	$sql7 = "UPDATE band_page SET bmonthly_plays = 0 WHERE bmonthly_plays != 0";
		$result7 = mysql_query($sql7) or die(mysql_error());


$sql2 = "SELECT bandid FROM monthly_plays GROUP BY bandid";
			$result2 = mysql_query($sql2) or die(mysql_error());
			while($row2= mysql_fetch_array($result2)){
			$bandid = $row2['bandid'];
$sql5 = "SELECT count(songid) AS score FROM monthly_plays WHERE bandid='$bandid'";
			$result3 = mysql_query($sql5) or die(mysql_error());
			while($row3= mysql_fetch_array($result3)){
			$score = $row3['score'];
		$sql7 = "UPDATE band_page SET bmonthly_plays = '$score' WHERE bandid = '$bandid'";
		$result7 = mysql_query($sql7) or die(mysql_error());
		}
		}

As you can see the first step is to delete all entries that are over a month old. The problem is that I'm still stuck with over a million entreies to have to sort through.

Does anybody have a better method than this? There has to be a more efficient way.

Thanks a lot.

[lucasrd]

Maybe save the dates as timestamps, make it an index....may save a little bit of processing time...

Also, why store stats in the different tables, why not just save all entries less than one month old in the same table and use PHP/Mysql to query the data and show the relevant entries???

[Dolemite50]

Hi and thanks,

I used two tables in hopes that it would be faster to sort through a weekly tableof 300,000 entries than querying a monthly table with over a million entries determining which are less than 7 days old.

Can you please explain the timestamp as an index idea to me?

I tried this at one point:

$getrevs = "SELECT songid FROM weekly_plays WHERE bandid='$bandid' AND songid='$songid' AND playdate=now()";
$rs5 = mysql_query($getrevs, $conn);
if(mysql_num_rows($rs5) > 0){
		$sql8 = "UPDATE weekly_plays SET score=score+1 WHERE bandid='$bandid' AND songid='$songid' AND playdate=now()";
        $result8 = mysql_query($sql8) or die(mysql_error());
		}
else{
		$sql8 = "INSERT INTO weekly_plays SET songid='$songid', playdate=now(), bandid = '$bandid', score=1";
		$result8 = mysql_query($sql8) or die(mysql_error());
}
$getrevs = "SELECT songid FROM monthly_plays WHERE bandid='$bandid' AND songid='$songid' AND playdate=now()";
$rs5 = mysql_query($getrevs, $conn);
if(mysql_num_rows($rs5) > 0){
		$sql8 = "UPDATE monthly_plays SET score=score+1 WHERE bandid='$bandid' AND songid='$songid' AND playdate=now()";
        $result8 = mysql_query($sql8) or die(mysql_error());
		}
else{
		$sql8 = "INSERT INTO monthly_plays SET songid='$songid', playdate=now(), bandid = '$bandid', score=1";
		$result8 = mysql_query($sql8) or die(mysql_error());

Where it would check to see if an entry for that date, songid, and bandid exists in the table and if so, add one to that value. I thought it would help cut down on the number of entries but it was making the query to INSERT too slow.

[Dolemite50]

I'm guessing maybe this one is asking too much from the server?

[Dolemite50]

Hmmmmm.