CODEWITHSUNDEEP
mysqlperformanceviewquery-optimization
yi1
yi1

Reputation: 413

How do I make query on MySQL view run faster?

This query takes 1.2 seconds:

select * from alert limit 10000;

This query takes 33.99 seconds:

select * from alert_version limit 10000;

alert_version is a view, which is basically the "alert" table with a subquery:

select  `alert`.`Alert_UID` AS `Alert_UID`,`alert`.`Rule_Name` AS `Rule_Name`,
        `alert`.`Headline` AS `Headline`,`alert`.`Severity` AS `Severity`,
        `alert`.`Device_UID` AS `Device_UID`,`alert`.`Configuration_Set_ID` AS `Configuration_Set_ID`,
        `alert`.`Instance_UID` AS `Instance_UID`,`alert`.`Create_DateTime` AS `Create_DateTime`,
        `alert`.`Delete_DateTime` AS `Delete_DateTime`,
        ( SELECT  `version_build`.`Version`
            from  `version_build`
            where  ((`version_build`.`Instance_UID` = `alert`.`Instance_UID`)
                      and  (`version_build`.`Create_DateTime` >= `alert`.`Create_DateTime`)
                   )
            order by  `version_build`.`Create_DateTime`
            limit  1
        ) AS `version`
    from  `alert`

When I run EXPLAIN on this query, I get:

+----+--------------------+---------------+------+-----------------------------------------------------------------------------------+--------------------------+---------+----------------------------+--------+------------------------------------------+
| id | select_type        | table         | type | possible_keys                                                                     | key                      | key_len | ref                        | rows   | Extra                                    |
+----+--------------------+---------------+------+-----------------------------------------------------------------------------------+--------------------------+---------+----------------------------+--------+------------------------------------------+
|  1 | PRIMARY            | alert         | ALL  | NULL                                                                              | NULL                     | NULL    | NULL                       | 301274 | NULL                                     |
|  2 | DEPENDENT SUBQUERY | version_build | ref  | uid_version_build_create,version_build_Instance_UID,version_build_Create_DateTime | uid_version_build_create | 110     | insight.alert.Instance_UID |      6 | Using where; Using index; Using filesort |
+----+--------------------+---------------+------+-----------------------------------------------------------------------------------+--------------------------+---------+----------------------------+--------+------------------------------------------+

So, what do you think I should do to get acceptable performance?

UPDATE:

Per request, adding table information:

CREATE TABLE `alert` (
`Alert_UID` varchar(36) NOT NULL,
`Rule_Name` varchar(80) DEFAULT NULL,
`Headline` varchar(255) DEFAULT NULL,
`Severity` varchar(12) DEFAULT NULL,
`Device_UID` varchar(36) NOT NULL,
`Configuration_Set_ID` varchar(12) DEFAULT NULL,
`Instance_UID` varchar(36) NOT NULL,
`Create_DateTime` timestamp NOT NULL,
`Delete_DateTime` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`Alert_UID`),
KEY `alert_Create_DateTime` (`Alert_UID`,`Create_DateTime`),
KEY `alert_Headline` (`Headline`),
KEY `alert_Rule_Headline` (`Rule_Name`,`Headline`),
KEY `alert_Instance_UID` (`Instance_UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


CREATE TABLE `version_build` (
`Instance_UID` varchar(36) NOT NULL,
`Version` varchar(12) NOT NULL,
`Build` varchar(30) DEFAULT NULL,
`Create_DateTime` timestamp NOT NULL,
UNIQUE KEY `uid_version_build_create` 
(`Instance_UID`,`Version`,`Build`,`Create_DateTime`),
KEY `version_build_Instance_UID` (`Instance_UID`),
KEY `version_build_Create_DateTime` (`Create_DateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Upvotes: 0

Views: 81

Answers (2)

m47730
m47730

Reputation: 2261

Please test carefully! I only made some quick test

From my point of view the subquery is just an overkill in this query (so the performances are horrible):

SELECT
`alert`.`Alert_UID` AS `Alert_UID`,
`alert`.`Rule_Name` AS `Rule_Name`,
`alert`.`Headline` AS `Headline`,
`alert`.`Severity` AS `Severity`,
`alert`.`Device_UID` AS `Device_UID`,
`alert`.`Configuration_Set_ID` AS `Configuration_Set_ID`,
`alert`.`Instance_UID` AS `Instance_UID`,
`alert`.`Create_DateTime` AS `Create_DateTime`,
`alert`.`Delete_DateTime` AS `Delete_DateTime`, 
`version_build`.`Version` AS `version`,
MIN(version_build.Create_DateTime)
FROM alert
LEFT JOIN version_build ON (alert.Instance_UID = version_build.Instance_UID
AND version_build.Create_DateTime >= alert.Create_DateTime)
GROUP BY alert.Alert_UID

Group-By is not order-wise so is necessary the trick of MIN in the select field otherwise the order is unpredictable. I didn't test the performance, but i guess they are a fraction of subquery.

Now with null values in case the join condition is not satisfied.

Upvotes: 1

Rick James
Rick James

Reputation: 142208

What will you do with 10K rows? That is usually too much for an app to handle.

Without an ORDER BY, which 10K rows are you hoping to get? It is unpredictable. With an ORDER BY, the query may be even slower.

The subquery is the performance killer. Live with the speed. However, you seem to be doing a "groupwise max" in an inefficient way. This index may help:

INDEX(Instance_UID, Create_DateTime, Version)   -- in this order!

More on groupwise-max: http://mysql.rjweb.org/doc.php/groupwise_max

Upvotes: 2

Related Questions