Optymalizacja zapytań Opcje

[sebekzosw]

Mam tabelę, w której znajduje się ponad 2.500.000 rekordów.

jeżeli wykonuje zapytanie sprawdzające źródła odwiedzin:

[SQL] pobierz, plaintext 
SELECT `domain`, COUNT(`id`) AS `count` FROM `all_cms_entry` GROUP BY `domain` ORDER BY `count` DESC
[SQL] pobierz, plaintext 

to trochę długo się wykonuje:

Pokaż wiersze 0 - 3 ( 4 wszystkich, Wykonanie zapytania trwało 8.5397 sekund(y))

Oto moja struktura bazy danych:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `all_cms_entry` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `page` varchar(50) COLLATE utf8_polish_ci DEFAULT NULL,
  `back` enum('0','1') COLLATE utf8_polish_ci DEFAULT '0',
  `domain` varchar(70) COLLATE utf8_polish_ci DEFAULT NULL,
  `referer` varchar(100) COLLATE utf8_polish_ci DEFAULT NULL,
  `browser` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `platform` varchar(20) COLLATE utf8_polish_ci DEFAULT NULL,
  `keyword` varchar(60) COLLATE utf8_polish_ci DEFAULT NULL,
  `ip` int(10) UNSIGNED DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `page` (`page`,`back`,`domain`,`browser`,  `platform`, `keyword`,`ip`), 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=2621940 ;
[SQL] pobierz, plaintext 

Da się to jakoś przyśpieszyć?

[nospor]

Skoro grupujesz po domain to wypadałoby na to pole założyć index

[sebekzosw]

hmm, indeks jest nałożony

[cv65]

Pokaż co Ci mówi EXPLAIN

[sebekzosw]

Struktura:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `all_cms_entry` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `page` varchar(50) COLLATE utf8_polish_ci DEFAULT NULL,
  `back` enum('0','1') COLLATE utf8_polish_ci DEFAULT '0',
  `domain` varchar(70) COLLATE utf8_polish_ci DEFAULT NULL,
  `referer` varchar(100) COLLATE utf8_polish_ci DEFAULT NULL,
  `browser` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `platform` varchar(20) COLLATE utf8_polish_ci DEFAULT NULL,
  `keyword` varchar(60) COLLATE utf8_polish_ci DEFAULT NULL,
  `ip` int(10) UNSIGNED DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `page` (`page`,`back`,`domain`,`browser`,`platform`,`keyword`,`ip`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3670017 ;
[SQL] pobierz, plaintext 

(IMG:http://imageshack.us/a/img580/4100/explainb.png)

[maly_swd]

to:
KEY `page` (`page`,`back`,`domain`,`browser`, `platform`, `keyword`,`ip`),

Nie oznacza ze masz nalozony index na domain!

Takie cos oznacza ze masz poprawnie;)
KEY `domain` (`domain`),

[sebekzosw]

[SQL] pobierz, plaintext 
SELECT `domain`, COUNT(`id`) AS `count` FROM `all_cms_entry` GROUP BY `domain` ORDER BY `count` DESC
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `all_cms_entry` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `page` varchar(50) COLLATE utf8_polish_ci DEFAULT NULL,
  `back` enum('0','1') COLLATE utf8_polish_ci DEFAULT '0',
  `domain` varchar(70) COLLATE utf8_polish_ci DEFAULT NULL,
  `referer` varchar(100) COLLATE utf8_polish_ci DEFAULT NULL,
  `browser` varchar(30) COLLATE utf8_polish_ci DEFAULT NULL,
  `platform` varchar(20) COLLATE utf8_polish_ci DEFAULT NULL,
  `keyword` varchar(60) COLLATE utf8_polish_ci DEFAULT NULL,
  `ip` int(10) UNSIGNED DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `domain` (`domain`),
  KEY `browser` (`browser`),
  KEY `platform` (`platform`),
  KEY `keyword` (`keyword`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=2097153 ;
[SQL] pobierz, plaintext 

w skrypcie pokazuje czas ładowania 4sec, jednak w phpmyadmin ładuje, ładuje i ładuje

(IMG:http://imageshack.us/a/img21/8817/localhostlocalhostphpmy.png)

jest dobrze?

[BaN]

Jest źle, baza nie korzysta z indeksu, zamień COUNT(`id`) na COUNT(*) i zobacz EXPLAIN, powinna skorzystać z indeksu. Jeśli dalej nie, to wykonaj polecenie

[SQL] pobierz, plaintext 
CHECK TABLE `all_cms_entry`
[SQL] pobierz, plaintext 

I jeszcze raz EXPLAIN. EXPLAIN powinien pokazać korzystanie z indeksu, dodatkowo pewnie dalej będzie tworzenie tabeli tymczasowej i filesort, bo masz sortowanie, ale powinno być szybkie jeśli założy tabelę w pamięci, a nie na dysku. Dużo jest tych unikalnych domen?

[sebekzosw]

"Using index; Using temporary; Using filesort" już jest ok w sumie

tych unikalnych domen w sumie to 3 są, jednak powieliłem to wszystko do testów (IMG:style_emoticons/default/smile.gif)

zamień COUNT(`id`) na COUNT(*)

zamieniłem i już czuć różnicę (IMG:style_emoticons/default/smile.gif)

ale powinno być szybkie jeśli założy tabelę w pamięci, a nie na dysku

da się ustawić, aby zakładało tabele w pamięci a nie na dysku? jeżeli tak to jak?

[maly_swd]

"da się ustawić, aby zakładało tabele w pamięci a nie na dysku? jeżeli tak to jak?"

Da sie, ale trzeba miec dostep do ustawien konfiguracyjnych mysql, sa tam odpowiednie zapisy dal tabel tymczasowych i wielkosci przydzielonego ramu.

[mmmmmmm]

ENGINE=MEMORY
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

[sebekzosw]

a może mi ktoś pomoże jeszcze z jednym problemem. Chce zrobić licznik średniego czasu spędzonego na stronie:

[SQL] pobierz, plaintext 
SELECT UNIX_TIMESTAMP(MAX(`date`))- UNIX_TIMESTAMP(MIN(`date`)) AS `sec`, `eid` FROM `all_cms_entry`
WHERE `eid` > 0
GROUP BY `eid`
ORDER BY `all_cms_entry`.`eid`  DESC
[SQL] pobierz, plaintext 

wynik to:

sec eid
7 2097172
2 2097171
9 2097170
4 2097169
14 2097168

a powinno być suma sekund / ilość rekordów

Średni czas to: 7.2


nie wiem jak to zsumować (IMG:style_emoticons/default/smile.gif)

[maly_swd]

normalnie powino byc to tak:

[SQL] pobierz, plaintext 
SELECT avg(UNIX_TIMESTAMP(MAX(`date`))- UNIX_TIMESTAMP(MIN(`date`)) ) AS `sec`, `eid` FROM `all_cms_entry`
WHERE `eid` > 0
GROUP BY `eid`
ORDER BY `all_cms_entry`.`eid`  DESC
[SQL] pobierz, plaintext 

pod warunkiem ze masz dobrze zrobiona baze:)

ale u Ciebie moze byc tez tak:

[SQL] pobierz, plaintext 
SELECT avg(UNIX_TIMESTAMP(MAX(`date`))- UNIX_TIMESTAMP(MIN(`date`)) ) AS `sec`, `eid` FROM `all_cms_entry`
WHERE `eid` > 0
ORDER BY `all_cms_entry`.`eid`  DESC
[SQL] pobierz, plaintext 

podaj co to jest to eid... i czemu po tym grupujesz?


Ten post edytował maly_swd 27.09.2012, 18:32:29

[sebekzosw]

jeżeli ktoś wejdzie za pierwszym razem na stronę to do bazy leci wpis o wejściu i inne dane. wtedy do sesji zapisuje id pierwszego wejścia (eid). Jeżeli wejdzie drugi raz to również zapisuje dane do bazy + eid. Domyślnie to pole ma ustawiona wartość 0

[maly_swd]

i jak? dziala Ci ten przyklad z AVG() co podalem?

[sebekzosw]

przykład nie działa, błąd #1111 ;/

[maly_swd]

[SQL] pobierz, plaintext 
SELECT avg(a.sec) FROM (SELECT UNIX_TIMESTAMP(MAX(`date`))- UNIX_TIMESTAMP(MIN(`date`)) AS `sec`, `eid` FROM `all_cms_entry`
WHERE `eid` > 0
GROUP BY `eid`
ORDER BY `all_cms_entry`.`eid`  DESC
) AS a
[SQL] pobierz, plaintext 

moim zdaniem cos masz namieszane w danych i zalozeniach, czemu liczycz to w taki sposob? UNIX_TIMESTAMP(MAX(`date`))- UNIX_TIMESTAMP(MIN(`date`)