Optymalizacja zapytania Opcje

[wlamywacz]

Witam

Struktura tabel:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `pages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` varchar(200) NOT NULL,
  `lastpr` smallint(2) NOT NULL,
  `pr` smallint(2) NOT NULL,
  `updateip` tinyint(1) UNSIGNED NOT NULL,
  `ip` bigint(20) UNSIGNED NOT NULL,
  `categoryid` int(11) UNSIGNED NOT NULL,
  `lastcheck` datetime NOT NULL,
  `updatepr` tinyint(1) UNSIGNED NOT NULL,
  `googlelastupdatesite` datetime NOT NULL,
  `googleupdatesite` tinyint(1) UNSIGNED NOT NULL,
  `googlesite` bigint(20) UNSIGNED NOT NULL,
  `yahoolastupdatesite` datetime NOT NULL,
  `yahooupdatesite` tinyint(1) UNSIGNED NOT NULL,
  `yahoosite` bigint(20) UNSIGNED NOT NULL,
  `yahoolastupdatebl` datetime NOT NULL,
  `yahooupdatebl` tinyint(1) UNSIGNED NOT NULL,
  `yahoobl` bigint(20) UNSIGNED NOT NULL,
  `information` text NOT NULL,
  `ping` int(11) NOT NULL,
  `lastping` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `status` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pageid` bigint(20) UNSIGNED NOT NULL,
  `date` datetime NOT NULL,
  `server` tinyint(1) NOT NULL,
  `php` tinyint(1) NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `pageid` (`pageid`,`date`)
)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
 
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
)
[SQL] pobierz, plaintext 

Zapytanie:

[SQL] pobierz, plaintext 
SELECT p.`id`, p.`url`, p.`ip`, p.`lastpr`, p.`pr`, p.`googlesite`, p.`yahoosite`, p.`yahoobl`, p.`ping`, p.`information`, p.`categoryid` AS `category`, c.`name` AS `categoryname`, s.`server`, s.`php`, INET_NTOA(p.`ip`) AS `ip`, (SELECT COUNT(st.`id`) FROM `status` AS `st` WHERE st.`pageid` = p.`id` AND (st.`server` = 0 OR st.`php` = 0) AND DATE_ADD(st.`date`, INTERVAL 7 DAY) > NOW()) AS `errors` FROM `pages` AS `p` LEFT JOIN `categories` AS `c` ON c.`id` = p.`categoryid` LEFT JOIN `status` AS `s` ON (s.`id` = (SELECT `id` FROM `status` WHERE `pageid` = p.id ORDER BY `date` DESC LIMIT 1))
[SQL] pobierz, plaintext 

Niestety w tabeli status ilość rekordów wynosi 500 tysięcy do miliona może nawet kilku w ekstremalnych przypadkach. Całe zapytanie wykonuje się około 2,5 sekundy przy 400k rekordów. Wąskimi gardłami w tym zapytaniu są te dwa pod zapytania jednak niezbyt mam pojęcie jak się ich pozbyć i zastąpić czymś bardziej optymalnym. Z góry dziękuje za pomoc.

[patryczakowy]

[SQL] pobierz, plaintext 
SELECT `id` FROM `status` WHERE `pageid` = p.id ORDER BY `date` DESC LIMIT 1
[SQL] pobierz, plaintext 

Po co tu order by??
z tego co pamiętam to lepiej zamiast NOW() w php wygenerować datę i dać do zapytania

[wlamywacz]

Bo muszę pobrać najmłodszy rekord.

[Mchl]

Masz podzapytania skorelowane, co zawsze jest wolne.
http://dev.mysql.com/doc/refman/5.1/en/cor...subqueries.html

Spróbuj to przepisać tak, żeby tego uniknąć (w większości przypadków da się)

[wlamywacz]

Użyje chyba nadmiarowych danych aby zwiększyć wydajność tego zapytania.

[eccocce]

Masz podzapytania skorelowane, co zawsze jest wolne.
http://dev.mysql.com/doc/refman/5.1/en/cor...subqueries.html

Spróbuj to przepisać tak, żeby tego uniknąć (w większości przypadków da się)

Masz może jakieś linki na ten temat? Bo też często używam zapytań skorelowanych (IMG:style_emoticons/default/smile.gif)

[Mchl]

Manual rozdział 7.2.
http://dev.mysql.com/doc/refman/5.1/en/query-speed.html

Ten post edytował Mchl 18.06.2010, 18:33:47

[yevaud]

lepiej zamiast NOW() wrzucic statyczna date z php, NOW jest niedeterministyczne i nie bardzo mysql potrafi je cachowac
jesli wrzucisz zamiast tego jakas stala, to wprawdzie zapytanie za pierwszym razem wykona sie 2.5 sec, ale za drugim juz 0.1 (IMG:style_emoticons/default/winksmiley.jpg)

wydaje mi sie ze ten count nie musi byc w podzapytaniu, chyba wystarczy zwykle grupowanie po p.id
najmniejsze statusy drugiego podzapytania przygotowalbym sobie wczesniej w tabeli tymczasowej i sklejal to inner

zawsze gdy robilem dane nadmiarowe to pozniej zalowalem (IMG:style_emoticons/default/smile.gif)

Ten post edytował yevaud 19.06.2010, 00:13:13

[wookieb]

A może nauczysz się formatować zapytanie tak żeby DAŁO JE SIĘ CZYTAĆ?

Po drugie nie masz założonych żadnych indeksów więc najwyższa pora o nich pomyśleć.
Uprzedzam pytania "na co założyć?". Nie jesteśmy w stanie DOBRZE tego określić ponieważ do analizy potrzebne jest znacznie więcej niż twoje jedno zapytanie.

[yevaud]

nie wiem dlaczego zalozylem milczaco, ze indeksy pozakladane sa oddzielnie (IMG:style_emoticons/default/smile.gif)
polecam prosty wpis o optymalizacji do przetrawienia

http://net.tutsplus.com/tutorials/other/to...best-practices/

[wlamywacz]

Poradziłem sobie już z nadmiarowymi danymi. Jeśli ktoś otworzy oczy to zauważy iż indexy są założone, bez nich zapytanie trwało by wieki. Pozdrawiam i dziękuje za pomoc. Temat do zamknięcia. (IMG:style_emoticons/default/smile.gif)

[Mchl]

Pytanie, czy to najlepsze indeksy jakie się da zrobić (IMG:style_emoticons/default/winksmiley.jpg)