Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Optymalizacja zapytania
Forum PHP.pl > Forum > Bazy danych > MySQL
wlamywacz
Witam

Struktura tabel:
  1. CREATE TABLE IF NOT EXISTS `pages` (
  2. `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  3. `url` varchar(200) NOT NULL,
  4. `lastpr` smallint(2) NOT NULL,
  5. `pr` smallint(2) NOT NULL,
  6. `updateip` tinyint(1) UNSIGNED NOT NULL,
  7. `ip` bigint(20) UNSIGNED NOT NULL,
  8. `categoryid` int(11) UNSIGNED NOT NULL,
  9. `lastcheck` datetime NOT NULL,
  10. `updatepr` tinyint(1) UNSIGNED NOT NULL,
  11. `googlelastupdatesite` datetime NOT NULL,
  12. `googleupdatesite` tinyint(1) UNSIGNED NOT NULL,
  13. `googlesite` bigint(20) UNSIGNED NOT NULL,
  14. `yahoolastupdatesite` datetime NOT NULL,
  15. `yahooupdatesite` tinyint(1) UNSIGNED NOT NULL,
  16. `yahoosite` bigint(20) UNSIGNED NOT NULL,
  17. `yahoolastupdatebl` datetime NOT NULL,
  18. `yahooupdatebl` tinyint(1) UNSIGNED NOT NULL,
  19. `yahoobl` bigint(20) UNSIGNED NOT NULL,
  20. `information` text NOT NULL,
  21. `ping` int(11) NOT NULL,
  22. `lastping` datetime NOT NULL,
  23. PRIMARY KEY (`id`),
  24. UNIQUE KEY `id` (`id`)
  25. )

  1. CREATE TABLE IF NOT EXISTS `status` (
  2. `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  3. `pageid` bigint(20) UNSIGNED NOT NULL,
  4. `date` datetime NOT NULL,
  5. `server` tinyint(1) NOT NULL,
  6. `php` tinyint(1) NOT NULL,
  7. UNIQUE KEY `id` (`id`),
  8. KEY `pageid` (`pageid`,`date`)
  9. )

  1.  
  2. CREATE TABLE IF NOT EXISTS `categories` (
  3. `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `name` varchar(80) NOT NULL,
  5. PRIMARY KEY (`id`),
  6. UNIQUE KEY `id` (`id`)
  7. )

Zapytanie:
  1. 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))


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
  1. SELECT `id` FROM `status` WHERE `pageid` = p.id ORDER BY `date` DESC LIMIT 1

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
Cytat(Mchl @ 16.06.2010, 20:05:26 ) *
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 smile.gif
Mchl
Manual rozdział 7.2.
http://dev.mysql.com/doc/refman/5.1/en/query-speed.html
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 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 smile.gif
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 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. smile.gif
Mchl
Pytanie, czy to najlepsze indeksy jakie się da zrobić winksmiley.jpg
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.