MySQL Forums
Forum List  »  Optimizer & Parser

Re: Close to a million records & query too slow
Posted by: Rick James
Date: March 09, 2010 09:20PM

Before:
select  artist.artistId, artist.name, count(play.playId) as plays
    from  charts.play play, charts.artist artist,
        charts.song song
    where  (play.songId = song.songId)
      and  (song.artistId = artist.artistId)
      and  (date(play.dateAndTime) >= ?)
      and  (date(play.dateAndTime) <= ?)
      and  (artist.isLocal = ?)
    group by  artist.artistId
    order by  plays desc;
After:
# do once:
ALTER TABLE play
   DROP INDEX FK_songId,            -- about to be redundant
   ADD INDEX(songId, dateAndTime);  -- for the subquery below
# The SELECT...
USE charts;
select  a.artistId,
        a.name,
        ( SELECT count(*) FROM play 
             WHERE  dateAndTime >= ?
               AND  dateAndTime <  DATE_ADD(?, INTERVAL 1 DAY)
               AND  songId = s.songId
        ) as plays
    from  artist a,
          song s
    where  (s.artistId = a.artistId)
      and  (a.isLocal = ?)
    order by  plays desc;
* Turn aggregrate into a subquery (so that the rest does not have to lug around lots of rows)
* COUNT(playid) checks each playid for NULL; COUNT(*) is simpler.
* Indexing isLocal is probably useless -- it is not very selective, right?
* dateAndTime is rearranged -- DATE(dateAndTime) is applying a function; this prevents using the index I added.
* < and 1 DAY are carefully chosen to make your range 'right'.

EXPLAIN SELECT ...
You will probably see that it will
1. do a table scan on artist
2. do "eq_ref" into song
3. repeatedly execute the correlated subquery. And it will be more efficient because of the new index, and say so via "Using index".

This will probably generate all the artists listed, together with "0 plays" for some of them. You could toss those by adding
HAVING plays > 0
before the ORDER BY.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Close to a million records & query too slow
2240
March 09, 2010 09:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.