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.

[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