Close to a million records & query too slow

[sabelo simelane]

Could you please assist; in one of my tables I have close to a million records and still growing. The query is too slow as I am doing a 3 table join. Here are the details:

DROP TABLE IF EXISTS `charts`.`artist`;
CREATE TABLE `charts`.`artist` (
`artistId` smallint(5) unsigned NOT NULL,
`name` varchar(150) NOT NULL,
`gender` varchar(10) DEFAULT NULL,
`isLocal` tinyint(1) unsigned DEFAULT NULL,
`picture` mediumblob NOT NULL,
PRIMARY KEY (`artistId`),
KEY `isLocal` (`isLocal`,`artistId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `charts`.`play`;
CREATE TABLE `charts`.`play` (
`playId` mediumint(8) unsigned NOT NULL,
`dateAndTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stationId` tinyint(3) unsigned NOT NULL,
`songId` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`playId`),
UNIQUE KEY `dateAndTime` (`dateAndTime`,`stationId`,`songId`),
KEY `FK_stationId2` (`stationId`),
KEY `FK_songId` (`songId`),
CONSTRAINT `FK_songId` FOREIGN KEY (`songId`) REFERENCES `song` (`songId`) ON UPDATE NO ACTION,
CONSTRAINT `FK_stationId2` FOREIGN KEY (`stationId`) REFERENCES `station` (`stationId`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `charts`.`song`;
CREATE TABLE `charts`.`song` (
`songId` smallint(5) unsigned NOT NULL,
`title` varchar(150) NOT NULL,
`artistId` smallint(5) unsigned NOT NULL,
`yesComId` mediumint(8) unsigned NOT NULL,
`coverUrl` varchar(150) DEFAULT NULL,
PRIMARY KEY (`songId`),
UNIQUE KEY `yescomId` (`yesComId`),
KEY `FK_artistId` (`artistId`),
CONSTRAINT `FK_artistId` FOREIGN KEY (`artistId`) REFERENCES `artist` (`artistId`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's the monster query:

select song.songId, song.title, artist.name, play.dateAndTime, count(play.playId) as plays, song.coverUrl from charts.play play, charts.artist artist, charts.song song where (play.songId = song.songId) and (song.artistId = artist.artistId) and (date(play.dateAndTime) >= '2010-02-10') and (date(play.dateAndTime) <= '2010-02-11') and (artist.isLocal is true) group by song.songId order by plays desc;

Here's the explanation of the query:

table | type | possible keys | key | key_len | ref | rows | Extra
---------------------------------------------------------------------
artist|ALL | PRIMARY | null| null | null | 7393 | Using filesort..
----------------------------------------------------------------------
song | ref |PRIMARY,FK_ar..|artistId| 2 |artistId | 1 |
-----------------------------------------------------------------------
play | ref | FK_SongId |songId | 2 |songId | 17 | Using where