Hi.
On Thu, Feb 15, 2001 at 09:25:56PM -0800, [EMAIL PROTECTED] wrote:
>
> > Could you please post the result of "EXPLAIN" for both queries and "SHOW INDEX
>FROM album_by_city".
>
>
> I haven't used EXPLAIN before.
Just put it before the SELECT in your query. I don't know whether this
will work directly in your program (php?), you can do it another way,
too. Let's take your first query:
SELECT SUM(total_plays) FROM album_by_city WHERE album_id = $album_id
AND city = $this_city AND date >= $StartDate AND date <= $EndDate;
You have to replace all variables by some real values. (best if you
simply put an additional echo in your php(?) program).
Assume $album_id=10, $this_city="New York", $StartDate="2001-01-01"
and $EndDate="2001-02-14" would be valid values (replace them by
whatever correct values are), then your select looks like
SELECT SUM(total_plays) FROM album_by_city WHERE album_id=10 AND
city="New York" AND date >= "2001-01-01" AND date <= "2001-02-14";
You can execute this in the mysql command line client. And then simply
add EXPLAIN to it:
EXPLAIN SELECT SUM(total_plays) FROM album_by_city WHERE album_id=10 AND
city="New York" AND date >= "2001-01-01" AND date <= "2001-02-14";
And do the same for the second query.
> There are indexes on each column.
That's not enough. To use index best with the above query, you would
need an combined index on (album_id,city,date) or maybe even
(album_id,city,date,total_plays), which maybe could avoid to read the
data file at all.
So, please post the result of SHOW INDEX FROM album_by_city, which
enables us to give better help (instead of guessing around).
> There are about 100 cities or regions that are used for ranking.
Hm. 200 queries, half a second per query does not seem too bad with
only a single column index.
> > Additionally, I would be interested, if you used the same method with
>
> > Oracle or another. If so, would you please post it, too.
>
> I didn't write the Oracle queries but they used sub selects and all
> kinds of not available in MySQL optimizations.
Again, if you are not forbidden to do so, please post it. Maybe it
took an approach I am not thinking of. It would render it unnecessary
that we have to start from scratch.
To be true, I am a bit nerved. I quite concretely asked for
information to be able to help, and you somehow did not bother to give
specific answers and I had to ask the same questions again.
I would appreciate, if you take the time to gather the information and
post what I asked for, even if you do not see immediate need, as I
have perfectly my reasons. Please do your part to enable us to do
ours. Thank you.
Bye,
Benjamin.
>
> Thanks for your reply.
>
> - Sam.
> Benjamin Pflugmann <[EMAIL PROTECTED]> wrote:
> 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
