Hi.
On Wed, Feb 21, 2001 at 12:32:21PM -0800, [EMAIL PROTECTED] wrote:
>
> I will respond in more detail later but just quickly now :
>
>
> > Hm. Sorry, I don't understand your argument. By the for loop, your
> >query does the same, doesn't it (selecting the rank of one album in
> >each metro is the same as selecting for all metros the rank of one album)?
>
> The subtle difference is that the query I'm using ( I didn't
> actually write it ) calculates the rank of the single album in
> question by figuring out how many albums have greater play totals &
> not caring what the more popular albums or what their totals
> were. Perhaps ignoring albums with smaller play totals using the
> HAVING & focusing on just one album's position helps a lot.
Ah. Okay. I thought about that meaning, but could not get positive
that you actually meant it this way.
Well. I doubt that this could be the reason, because HAVING is applied
last, i.e. your query executes (almost) as if there was no HAVING
clause, and then HAVING is applied (in fact, it is a bit different,
but the main point is that _all_ albums will be examined and only the
ones with a higher total_play will be returned).
As I said, I guess it's rather the fact, that about 100 times more
data has to be processed in _one_ turn, and MySQL decides to stores
the intermediate result on disk (I believe, the default limit for
in-memory temporary tables is 1MB, but it might be that I am just
mixing up things here).
[...]
> > I am really curious, whether my arguments hold, and therefore what
> > your test results are. Thank you in advance.
>
> If you are sufficiently curious I could send you a little Perl
> script that will create the ALBUM_BY_METRO table, populate it with
> test data & allow you to more easily analyze the problem. Would you
> like that ?
Yes, I would. Be sure to include enough data (so that the optimizer
may not attempt to do a full table scan due to few rows). If this gets
to big, just send it privately to me (or make it available for
download on the web).
It could take some days, because I am just upgrading my mysql version
and have to do some regression tests on my software first.
By the way, which MySQL version do you use?
Bye,
Benjamin.
---------------------------------------------------------------------
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
