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.