Erik Edgren Erik Edgren - 3 months ago 10
MySQL Question

Optimize SQL query with multiple SELECT's

The following SQL query slows down my website extremely much! From 0-1.5 seconds to easily over 20 seconds. How can I optimize it?

SELECT DATE(a.datetime_logged) AS date,
(SELECT COUNT(aa.data_status) FROM activity AS aa WHERE aa.id_user = '1' AND DATE(aa.datetime_logged) != CURDATE() AND DATE(a.datetime_logged) = DATE(aa.datetime_logged) AND aa.data_status = 'online') AS status_a,
(SELECT COUNT(aa.data_status) FROM activity AS aa WHERE aa.id_user = '1' AND DATE(aa.datetime_logged) != CURDATE() AND DATE(a.datetime_logged) = DATE(aa.datetime_logged) AND aa.data_status = 'idle') AS status_i,
(SELECT COUNT(aa.data_status) FROM activity AS aa WHERE aa.id_user = '1' AND DATE(aa.datetime_logged) != CURDATE() AND DATE(a.datetime_logged) = DATE(aa.datetime_logged) AND aa.data_status = 'streaming') AS status_s

FROM activity AS a
GROUP BY DATE(a.datetime_logged)
ORDER BY DATE(a.datetime_logged) DESC
LIMIT 40


Here's the table:

CREATE TABLE IF NOT EXISTS `activity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_user` int(11) NOT NULL,
`id_channel` varchar(50) NOT NULL,
`id_game` int(11) NOT NULL,
`data_muted_server` tinyint(4) NOT NULL,
`data_muted_self` tinyint(4) NOT NULL,
`data_deafen_server` tinyint(4) NOT NULL,
`data_deafen_self` tinyint(4) NOT NULL,
`data_suppressed` tinyint(4) NOT NULL,
`data_status` varchar(10) NOT NULL,
`data_game` text,
`datetime_logged` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `index_datelog` (`datetime_logged`)
)

Answer

you can use sum(case data_status when 'online' then 1 end) as status_a to replace nested heavy sub-queries