Zliczanie unikalnych wartości, Optymalizacja Opcje

[snapshot]

Chcę wyciągnąć ilość unikalnych osób, które głosowały. Zakładamy, że użytkownik może głosować kilka razy.

[SQL] pobierz, plaintext 
SELECT COUNT(DISTINCT user) FROM vote WHERE type = 5
[SQL] pobierz, plaintext 

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? (IMG:style_emoticons/default/smile.gif)

[nospor]

a masz założony index na type?

[Crozin]

Zapewne masz jakąś tabelę która reprezentuje sondę na którą mogą głosować użytkownicy, tak? Dodaj do niej dwie kolumny total_votes oraz total_unique_votes. Następnie dodaj sobie wyzwalacz, który po dodaniu rekordu do tabeli vote zwiększy wartości obu kolumn i analogicznie przy usunięciu zmniejszy je. Dzięki temu w ogóle nie będziesz musiał dynamicznie zliczać tych głosów.

EDIT: Dopiero teraz zauważyłem "type = 5" - w takim razie raczej powyższe będzie bezużyteczne.

Ten post edytował Crozin 24.05.2011, 10:52:14

[wookieb]

Jak już to na type, user (2 kolumny w jednym indeksie)

[snapshot]

Wpadłem na to sam @wookieb i faktycznie działa. Mam teraz 0.02. Dzięki. Jednak mam kolejny problem. Muszę podczas realizacji innego scenariusza (unikalne grupy) podłączyć inną tabelę:

[SQL] pobierz, plaintext 
SELECT COUNT(DISTINCT u.team) FROM vote v INNER JOIN user u USING(user) WHERE v.type = 5
[SQL] pobierz, plaintext 

Czas wykonywania to znowu ~1 sekundy. Analogicznie dodałbym index na dwóch kolumnach w dwóch różnych tabelach, ale chyba się nie da...

Ten post edytował snapshot 24.05.2011, 11:36:50

[wookieb]

Tabela user ma index na "user"?
user to login czy id usera? Jezeli login to zacznij używać id.
Silnik tabel polecam ustawić na INNODB.

[snapshot]

Kolumna user w tabli user to int Primary key. Silnik InnoDB

[wookieb]

Wrzuć prosze explain z zapytania

[SQL] pobierz, plaintext 
EXPLAIN twoj_select
[SQL] pobierz, plaintext 

oraz schematy tabel user,vote

[SQL] pobierz, plaintext 
SHOW CREATE TABLE `user`;
SHOW CREATE TABLE `vote`';
[SQL] pobierz, plaintext 

[snapshot]

Dla poprzenich przykładów zmieniałem nazwy tabeli, żeby można było się połapać. Teraz pozwolę sobie zostawić oryginały.

[SQL] pobierz, plaintext 
EXPLAIN SELECT COUNT(DISTINCT s.idartist) FROM on_air o INNER JOIN song s USING(idsong) WHERE o.idradio = 5
[SQL] pobierz, plaintext 

Kod

+----+-------------+-------+--------+------------------------------------------------+---------------------+---------+----------------+-------+-------+
| id | select_type | table | type   | possible_keys                                  | key                 | key_len | ref            | rows  | Extra |
+----+-------------+-------+--------+------------------------------------------------+---------------------+---------+----------------+-------+-------
|  1 | SIMPLE      | o     | ref    | fk_Onair_Radio1_idx,fk_Onair_Song1_idx,idradio | fk_Onair_Radio1_idx | 1       | const          | 49470 |       |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                                        | PRIMARY             | 3       | radio.o.idsong |     1 |       |
+----+-------------+-------+--------+------------------------------------------------+---------------------+---------+----------------+-------+-------+
2 rows in set (0.14 sec)

[SQL] pobierz, plaintext 
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
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
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
[SQL] pobierz, plaintext