Re: Close to a million records & query too slow

[Rick James]

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