Hi,
I have a table (STATS) which tracks 30 days worth of 7 differenet song statistics (songid, date, dowloads, plays, etc) for each song on the site. To save on space I am using tiny int for the columns and incrementing by one WHERE date=today when triggered. A daily cron then tallies and updates the song_table like so:
--DELETE all records > 30 days old
--Get SUM of each column for every song
--Multiplies these amounts by the appropriate "worth" (i.e. if a play is worth 10 points and the song has 10 plays that month; it returns 100)
--Updates the song_table with current "stats for last 30 days".
There are 10k+songs in the site so it's a fairly large task. I've read that php is better for multiplication so would you advise using this:
...SELECT SUM(plays) as plays, SUM(downloads) as downloads..
$plays = ($row['plays'] * 10);
or
...SELECT plays, downloads..
$plays = (($row['plays'] + 10)* 10); ....?
I know I can test those with a timer but there's another factor. In order to keep some heat off of the song_table, would you advise that I export all of the results to a temp table first? That way I could just use a quick "REPLACE INTO song_table (SELECT plays, downloads, ... FROM tmp_table)"
...once all the number crunching is done.
Or possibly better to use the PHP method
$plays = ($row['plays'] * 10); ..and store all of the totals in an array to update when finished?
Thank you very much for any advice.
