I'm moving over a music play database from Oracle to MySQL. So far MySQL is faster for
all reports except this one where Oracle takes 8 seconds & MySQL will take 108 with my
current method.
I have a big table of times that an album is played like so :
album_id INT,
total_plays INT,
date DATETIME,
city_id INT
I want to report the ranking of an album's plays (against all other albums ) for every
city over a time period.
National album ranking for "Beatles #1" between 1/1/2001 and 2/14/2001 :
New York #1
Chicago #2
Boston #1
Miami #3
Right now the current approach involves looping over every city & making 2 queries.
First query to get the total # of plays for this album for the time period in that
city. Next query to figure out how many other albums for that city & for that time
have total play sums that are greater, which is the Rank.
foreach $cities as $this_city
{
$this_album_total_plays = "select sum(total_plays) from album_by_city
where album_id = $album_id
and city = $this_city
and date >= $StartDate and date <= $EndDate";
$rank = "select album_id, sum(total_plays) from album_by_city
where city = $this_city
and date >= $StartDate and date <= $EndDate
group by album_id
having sum(total_plays) > $this_album_total_plays";
echo "$this_city $rank";
}
Is there a way to do this with fewer queries or perhaps temporary tables for greater
speed ?
thanks,
- Sam.
---------------------------------
Do You Yahoo!?
- Get personalized email addresses from Yahoo! Mail Personal Address - only $35 a
year!
