Hi.
Could you please post the result of "EXPLAIN" for both queries and
"SHOW INDEX FROM album_by_city".
And, for how much cities does this gets executed?
Additionally, I would be interested, if you used the same method with
Oracle or another. If so, would you please post it, too.
Bye,
Benjamin.
On Thu, Feb 15, 2001 at 10:47:16AM -0800, [EMAIL PROTECTED] wrote:
>
> 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 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 ?
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
