Slow query - just error count

Question

I deal with slow queries in whole project. I indexed heavily but the results are nowhere. I had a lot of logs (thousands per minute) from SCADA systems and I am trying to count them for each SCADA system and shows them in simple table. But the problem is that each subquery I have in my main query is pretty fast when run independently but when in one piece the query takes about 50 seconds and more.

I made this fix - I had fulltext searches on InnoDB table (logs_mgmt) to search all messages of SCADA system to find the error one for each SCADA. It killed MySQL so I made an trigger which fires when data is stored to main table - logs_mgmt. This trigger stores error messages to new table logs_last_three_days_mgmt. There is an event which fires every day at morning and clears all rows older than 3 days

Is there a way how to improve this select? How should I change this query to improve its performance?

My query:

SELECT scada_systems_mgmt.scada_system_id, 
                                          scada_systems_mgmt.scada_name, 
                                          scada_systems_mgmt.scada_description, 
                                          scada_systems_mgmt.scada_url,
                                          (
                                              SELECT COUNT(*)
                                              FROM error_logs_mgmt
                                              WHERE error_logs_mgmt.scada_id = scada_systems_mgmt.scada_system_id
                                              AND error_logs_mgmt.real_dt > DATE_SUB(NOW(), INTERVAL 3 DAY)
                                              AND error_logs_mgmt.id > 
                                              IF(
                                                  (
                                                        SELECT @ret_id = error_logs_mgmt.id
                                                        FROM error_logs_mgmt
                                                        WHERE error_logs_mgmt.scada_id = scada_systems_mgmt.scada_system_id
                                                        AND solved=1
                                                        ORDER BY id DESC
                                                        LIMIT 1
                                                  ) IS NOT NULL,
                                                  @ret_id,
                                                  (
                                                        SELECT MIN(id)
                                                        FROM error_logs_mgmt
                                                        WHERE error_logs_mgmt.scada_id = scada_systems_mgmt.scada_system_id
                                                  )
                                             )
                                          ) AS scada_errors_count,
                                          (
                                              SELECT COUNT(alarms_mgmt.id)
                                              FROM alarms_mgmt
                                              WHERE alarms_mgmt.scada_id = scada_systems_mgmt.scada_system_id
                                              AND alarms_mgmt.solved = 0
                                          ) as scada_alarms,
                                          users_scada_login_mgmt.scada_login,
                                          users_scada_login_mgmt.scada_password
                                      FROM scada_systems_mgmt
                                      LEFT JOIN (
                                                    SELECT  users_scada_login_mgmt.scada_login,
                                                            users_scada_login_mgmt.scada_password,
                                                            users_scada_login_mgmt.scada_system_id
                                                    FROM users_scada_login_mgmt
                                                    WHERE users_scada_login_mgmt.user_id = " . (int)$user_id . "
                                                ) users_scada_login_mgmt
                                          ON users_scada_login_mgmt.scada_system_id = scada_systems_mgmt.scada_system_id
                                      ORDER BY users_scada_login_mgmt.scada_login DESC

The logs_last_three_days same like logs_mgmt:

CREATE TABLE `logs_last_three_days_mgmt` (
 `id` bigint(20) NOT NULL,
 `scada_id` int(11) NOT NULL,
 `dt` datetime NOT NULL,
 `real_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `message` text NOT NULL,
 `solved` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `scada_id` (`scada_id`),
 KEY `solved` (`solved`),
 KEY `real_dt` (`real_dt`),
 CONSTRAINT `logs_last_three_days_mgmt_ibfk_1` FOREIGN KEY (`scada_id`) REFERENCES `scada_systems_mgmt` (`scada_system_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

In other tables there are only few dozens of rows so they are not the performace killer I think.

Answer 1

I would break these queries and have result stored in temporary tables. I would use a stored proc. Have sub-queries like this among many tables will create constrain and potentially some lockings. Also using > on a column in query will for Mysql to do a scan on that column (even if it is indexed). Avoid >,>= or < <= (use between).