0

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.

0

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).

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.