MySQL Forums
Forum List  »  Optimizer & Parser

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

select  s.songId, s.title, a.name,
        p.dateAndTime,
        count(p.playId) as plays,
        s.coverUrl
    from  charts.play p,
          charts.artist a,
          charts.song s
    where  (p.songId = s.songId)
      and  (s.artistId = a.artistId)
      and  (date(p.dateAndTime) >= '2010-02-10')
      and  (date(p.dateAndTime) <= '2010-02-11')
      and  (a.isLocal is true)
    group by  s.songId
    order by  plays desc;
Change the date comparison to this, so that you can use the index on dateAndTime:
      and  p.dateAndTime >= '2010-02-10'
      and  p.dateAndTime < DATE_ADD('2010-02-10', INTERVAL 1 DAY)

You are grouping by songId; what value do you expect for p.dateAndTime ? You will get one of the values at random (possibly the oldest). Because of that confusion, I can't quite say if the following give you the 'right' answer:

select  s.songId, s.title, a.name,
        p.dateAndTime AS first_play,
        plays,
        s.coverUrl
    from  charts.artist a,
          charts.song s,
          ( SELECT  MIN(dateAndTime) as dateAndTime,
                    count(*) as plays
                FROM charts.play
                WHERE  p.dateAndTime >= '2010-02-10'
                  and  p.dateAndTime < DATE_ADD('2010-02-10', INTERVAL 1 DAY)
                GROUP BY songId
          ) as p
    where  (p.songId = s.songId)
      and  (s.artistId = a.artistId)
      and  (a.isLocal is true)
    order by  plays desc;

This rewrite avoids reaching into chart.song multiple times if the same song is played multiple times

Options: ReplyQuote


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


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.