Struktura tabel:
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`) )
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`) )
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`) )
Zapytanie:
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.