SELECT COUNT(DISTINCT user) FROM vote WHERE type = 5
Powyższe zapytanie działa tak jak chcę, jednak czas wykonywania jest nie do zaakceptowania i wynosi 0.8-1.0 sekundy. Dodam, że tabela posiada aktualnie około 600 000 rekordów. Da się szybciej?
SELECT COUNT(DISTINCT user) FROM vote WHERE type = 5
SELECT COUNT(DISTINCT u.team) FROM vote v INNER JOIN user u USING(user) WHERE v.type = 5
EXPLAIN SELECT COUNT(DISTINCT s.idartist) FROM on_air o INNER JOIN song s USING(idsong) WHERE o.idradio = 5
CREATE TABLE `song` ( `idsong` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT, `title` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `redirect` mediumint(8) UNSIGNED DEFAULT NULL, `img_small` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `img_big` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `description` mediumblob, `lyric` mediumblob, `youtube` varchar(33) COLLATE utf8_unicode_ci DEFAULT NULL, `last_data_check` date NOT NULL, `idartist` mediumint(8) UNSIGNED NOT NULL, `slug` varchar(50) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`idsong`), UNIQUE KEY `un_Song_title_idArtist_idx` (`title`,`idartist`), UNIQUE KEY `IN_Song_slug_idx` (`slug`), KEY `fk_Song_Artist_idx` (`idartist`), KEY `reditect_idx` (`redirect`), CONSTRAINT `song_idartist_artist_idartist` FOREIGN KEY (`idartist`) REFERENCES `artist` (`idartist`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `song_redirect_song_idsong` FOREIGN KEY (`redirect`) REFERENCES `song` (`idsong`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=25692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `on_air` ( `idonair` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT, `time` datetime NOT NULL, `idradio` tinyint(3) UNSIGNED NOT NULL, `idsong` mediumint(8) UNSIGNED NOT NULL, PRIMARY KEY (`idonair`), KEY `fk_Onair_Radio1_idx` (`idradio`), KEY `fk_Onair_Song1_idx` (`idsong`), KEY `idradio` (`idradio`,`idsong`), CONSTRAINT `on_air_idradio_radio_idradio` FOREIGN KEY (`idradio`) REFERENCES `radio` (`idradio`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `on_air_idsong_song_idsong` FOREIGN KEY (`idsong`) REFERENCES `song` (`idsong`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=639365 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci