1

Edit: I am using MySQL 5.7.22

I have a stored procedure, when it is running it will have up to 1 millions rows examined. Which cause the 99% cpu usage. I want to optimize the query but I have no idea. Please suggest how I can optimize the stored procedure and reduced the rows examined when it is running.

CREATE DEFINER=`root`@`%` PROCEDURE `SelectUsage4`(

      IN p_ids MEDIUMTEXT
    , IN p_locationIDs MEDIUMTEXT
    , IN p_indicatorIDs MEDIUMTEXT
    , IN p_fromDate date
    , IN p_toDate date
    , IN p_yearly tinyint(4)
    , IN p_monthly tinyint(4)
    , IN p_halfYear1 tinyint(4)
    , IN p_halfYear2 tinyint(4)
    , IN p_fourMonths1 tinyint(4)
    , IN p_fourMonths2 tinyint(4)
    , IN p_fourMonths3 tinyint(4)
    , IN p_q1 tinyint(4)
    , IN p_q2 tinyint(4)
    , IN p_q3 tinyint(4)
    , IN p_q4 tinyint(4)
    , IN p_biMonthly1 tinyint(4)
    , IN p_biMonthly2 tinyint(4)
    , IN p_biMonthly3 tinyint(4)
    , IN p_biMonthly4 tinyint(4)
    , IN p_biMonthly5 tinyint(4)
    , IN p_biMonthly6 tinyint(4)
    , IN p_approvalStatus int(11)
    , IN p_language nvarchar(10)
)
BEGIN
    select 
        case when (select count(lbl.id) from `labels` as lbl where lbl.ObjectID = l.id and lbl.ObjectName = 'locations' and lbl.ColumnName = 'LocationName' and lbl.LanguageCode = p_language) > 0 then
            (select content from `labels` as lbl where lbl.ObjectID = l.id and lbl.ObjectName = 'locations' and lbl.ColumnName = 'LocationName' and lbl.LanguageCode = p_language limit 1)
        else
            l.LocationName
        end as LocationName
        , l.ParentID as LocationParentID
        , l.Active as LocationActive
        , l.RegionID
        , case when (select count(lbl.id) from `labels` as lbl where lbl.ObjectID = i.id and lbl.ObjectName = 'indicators' and lbl.ColumnName = 'IndicatorName' and lbl.LanguageCode = p_language) > 0 then
            (select content from `labels` as lbl where lbl.ObjectID = i.id and lbl.ObjectName = 'indicators' and lbl.ColumnName = 'IndicatorName' and lbl.LanguageCode = p_language limit 1)
        else
            i.IndicatorName
        end as IndicatorName
        , i.ParentID as IndicatorParentID
        , i.Unit
        , i.DecimalPlaces
        , i.Active as IndicatorActive
        , i.IndicatorType
        , u.*
    from
        `usage` as u
        left join `locations` as l on u.LocationID = l.id
        left join `Indicators` as i on u.IndicatorID = i.id
    where
        u.IsDeleted = 0
        and (
            (p_fromDate is null and p_toDate is null)
            or
            (
                p_fromDate is not null and p_toDate is not null
                and
                DATE(CONCAT(convert(u.`Year`, char(4)), '-', convert(u.`Month`, char(2)), '-1')) between p_fromDate and p_toDate
            )
            or
            (
                p_fromDate is not null and p_toDate is not null
                and
                u.`Month` is null
                and
                u.`Year` between Year(p_fromDate) and Year(p_toDate)
            )
        )
        and (p_yearly is null or (p_yearly is not null and p_yearly = 1 and u.`Month` is null) or (p_yearly is not null and p_yearly = 0 and u.`Month` is not null))
        and (p_monthly is null or (p_monthly is not null and p_monthly = 1 and u.`Month` is not null))
        and (p_ids is null or FIND_IN_SET(u.id, p_ids))
        and (p_locationIDs is null or FIND_IN_SET(u.LocationID, p_locationIDs))
        and (p_indicatorIDs is null or FIND_IN_SET(u.IndicatorID, p_indicatorIDs))
        and
        (

               (p_halfYear1 is null or u.HalfYear1 = p_halfYear1)
            or (p_halfYear2 is null or u.HalfYear2 = p_halfYear2)
        )
        and
        (
               (p_fourMonths1 is null or u.FourMonths1 = p_fourMonths1)
            or (p_fourMonths2 is null or u.FourMonths2 = p_fourMonths2)
            or (p_fourMonths3 is null or u.FourMonths3 = p_fourMonths3)
        )
        and
        (
               (p_q1 is null or u.Q1 = p_q1)
            or (p_q2 is null or u.Q2 = p_q2)
            or (p_q3 is null or u.Q3 = p_q3)
            or (p_q4 is null or u.Q4 = p_q4)
        )
        and
        (
               (p_biMonthly1 is null or u.BiMonthly1 = p_biMonthly1)
            or (p_biMonthly2 is null or u.BiMonthly2 = p_biMonthly2)
            or (p_biMonthly3 is null or u.BiMonthly3 = p_biMonthly3)
            or (p_biMonthly4 is null or u.BiMonthly4 = p_biMonthly4)
            or (p_biMonthly5 is null or u.BiMonthly5 = p_biMonthly5)
            or (p_biMonthly6 is null or u.BiMonthly6 = p_biMonthly6)
        )
        and (
                p_approvalStatus is null
                or
                (
                    select ara.ApprovalStatus
                    from `tasks_details` as t
                    inner join `approval_request_tasks` as art on t.TaskID = art.TaskID
                    inner join `approval_request_approvers` as ara on art.ApprovalRequestID = ara.ApprovalRequestID
                    where
                        t.IsDeleted = 0 
                        and 
                        t.ObjectID = u.id
                        and t.ObjectType = 'Usage'
                    order by
                        ara.ModifiedDate desc limit 1
                ) = p_approvalStatus
            )
    order by
        i.IndicatorName, l.LocationName
;

END

And here is the explain plan for the stored procedure

+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
| id |    select_type     | TABLE |  TYPE  | possible_keys  |   KEY   | key_len |                ref                | ROWS  | Extra  |                                              |
+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
|  1 | PRIMARY            | u     | ref    |index4,index7   | index4  | 1       | const                             | 37547 | 100.00 | USING WHERE; USING TEMPORARY; USING filesort |
|  1 | PRIMARY            | l     | eq_ref | PRIMARY,Index1 | PRIMARY | 4       | pg.u.LocationID                   |     1 | 100.00 |                                              |
|  1 | PRIMARY            | i     | eq_ref | PRIMARY        | PRIMARY | 4       | pg.u.IndicatorID                  |     1 | 100.00 |                                              |
|  6 | DEPENDENT SUBQUERY | ara   | INDEX  | Index1         | Index1  | 28      | (NULL)                            |  1384 | 100.00 | USING INDEX; USING filesort                  |
|  6 | DEPENDENT SUBQUERY | art   | ref    | Index1         | Index1  | 4       | pg.ara.ApprovalRequestID          |     1 | 100.00 | USING INDEX                                  |
|  6 | DEPENDENT SUBQUERY | t     | ref    | Index1         | Index1  | 161     | pg.art.TaskID,pg.u.id,const,const |     1 | 100.00 | USING INDEX                                  |
|  5 | DEPENDENT SUBQUERY | lbl   | ref    | Index1         | Index1  | 644     | const,pg.i.id,const,const         |     1 | 100.00 |                                              |
|  4 | DEPENDENT SUBQUERY | lbl   | ref    | Index1         | Index1  | 644     | const,pg.i.id,const,const         |     1 | 100.00 | USING INDEX                                  |
|  3 | DEPENDENT SUBQUERY | lbl   | ref    | Index1         | Index1  | 644     | const,pg.l.id,const,const         |     1 | 100.00 |                                              |
|  2 | DEPENDENT SUBQUERY | lbl   | ref    | Index1         | Index1  | 644     | const,pg.l.id,const,const         |     1 | 100.00 | USING INDEX                                  |
+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+

And here is the slow query log:

# administrator command: Init DB;
# Time: 2019-10-04T02:02:44.681457Z
# User@Host: root[root] @  [13.186.31.86]  Id:   258
# Query_time: 1.458692  Lock_time: 0.000000 Rows_sent: 233  Rows_examined: 598873
SET timestamp=1570154564;
CALL `SelectUsage4`(NULL, '65,92,207,93,94,95,66,67,372,57,96,68,70,69,71,378,379,380,97,98,370,250,99,196,100,197,208,63,183,72,51,74,75,101,73,64,395,251,102,103,104,252,106,209,105,210,429,257,107,258,91,46,267,108,211,259,253,261,254,260,255,109,110,79,80,81,437,111,112,427,428,409,113,413,412,425,28,41,249,114,212,333,335,366,334,368,367,318,391,406,43,115,213,263,116,214,215,117,216,118,217,119,120,121,122,124,218,123,125,126,127,128,129,130,131,219,56,220,221,198,132,133,48,134,222,223,224,135,136,137,225,50,138,271,331,417,414,363,226,139,227,315,140,141,229,199,228,142,143,144,230,146,231,147,78,148,149,316,150,151,264,45,268,232,233,152,269,153,154,200,155,443,234,201,156,157,76,265,49,342,235,236,158,159,160,161,237,238,162,77,163,394,390,439,442,389,388,415,416,418,419,420,387,424,410,421,369,426,239,164,240,272,314,202,241,266,273,165,166,167,203,242,47,270,168,444,169,204,86,328,170,274,243,171,87,374,375,376,377,373,244,275,172,205,371,385,386,173,256,42,174,175,176,245,177,178,277,287,279,288,286,291,317,280,289,284,281,282,295,290,283,292,293,294,285,278,179,246,206,180,276,247,181,88,52,89,182,248,184,185,186,187,188,189,190,191,192,193,194,195,407', '10065', '2018-11-01', '2018-11-30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 202, 'en-US');

The table structure:

CREATE TABLE `usage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `LocationID` int(11) NOT NULL,
  `IndicatorID` int(11) NOT NULL,
  `Year` int(11) DEFAULT NULL,
  `Month` int(11) DEFAULT NULL,
  `HalfYear1` tinyint(4) DEFAULT NULL,
  `HalfYear2` tinyint(4) DEFAULT NULL,
  `FourMonths1` tinyint(4) DEFAULT NULL,
  `FourMonths2` tinyint(4) DEFAULT NULL,
  `FourMonths3` tinyint(4) DEFAULT NULL,
  `Q1` tinyint(4) DEFAULT NULL,
  `Q2` tinyint(4) DEFAULT NULL,
  `Q3` tinyint(4) DEFAULT NULL,
  `Q4` tinyint(4) DEFAULT NULL,
  `BiMonthly1` tinyint(4) DEFAULT NULL,
  `BiMonthly2` tinyint(4) DEFAULT NULL,
  `BiMonthly3` tinyint(4) DEFAULT NULL,
  `BiMonthly4` tinyint(4) DEFAULT NULL,
  `BiMonthly5` tinyint(4) DEFAULT NULL,
  `BiMonthly6` tinyint(4) DEFAULT NULL,
  `DateOfUsage` date DEFAULT NULL,
  `Price` decimal(24,10) DEFAULT NULL,
  `PriceUnit` int(11) DEFAULT NULL,
  `ExchangeRate` decimal(24,10) DEFAULT NULL,
  `Value` decimal(24,10) DEFAULT NULL,
  `ValueUnit` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `Remarks` varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ModifiedDate` datetime DEFAULT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  `IsHeatRecovery` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Index1` (`LocationID`,`IndicatorID`,`Year`,`Month`,`HalfYear1`,`HalfYear2`,`FourMonths1`,`FourMonths2`,`FourMonths3`,`Q1`,`Q2`,`Q3`,`Q4`,`IsDeleted`,`CreatedDate`,`ModifiedDate`),
  KEY `Index2` (`LocationID`,`IndicatorID`,`Year`,`BiMonthly1`,`BiMonthly2`,`BiMonthly3`,`BiMonthly4`,`BiMonthly5`,`BiMonthly6`,`CreatedDate`,`ModifiedDate`,`IsDeleted`),
  KEY `Index3` (`LocationID`,`IndicatorID`,`DateOfUsage`,`IsDeleted`),
  KEY `index4` (`IsDeleted`),
  KEY `index5` (`LocationID`),
  KEY `index6` (`IndicatorID`),
  KEY `index7` (`Year`),
  KEY `index8` (`Month`)
) ENGINE=InnoDB AUTO_INCREMENT=79273 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `indicators` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ParentID` int(11) DEFAULT NULL,
  `IndicatorName` varchar(255) CHARACTER SET utf8 NOT NULL,
  `IndicatorType` int(11) DEFAULT NULL,
  `Unit` varchar(100) CHARACTER SET utf8 NOT NULL,
  `DecimalPlaces` int(11) DEFAULT NULL,
  `Active` tinyint(4) NOT NULL DEFAULT '1',
  `CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ModifiedDate` datetime DEFAULT NULL,
  `IsDeleted` tinyint(4) NOT NULL DEFAULT '0',
  `SyncID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LastSyncDate` datetime DEFAULT NULL,
  `FormulaSummary` int(11) DEFAULT NULL,
  `IndicatorCategory` int(11) DEFAULT NULL,
  `BreakSync` tinyint(4) DEFAULT NULL,
  `IsInteger` tinyint(1) DEFAULT '0',
  `ActiveForReporting` tinyint(1) DEFAULT '1',
  `BaselineYear` int(11) DEFAULT NULL,
  `Ceiling` decimal(24,10) DEFAULT NULL,
  `Floor` decimal(24,10) DEFAULT NULL,
  `BreakSyncForUnit` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Index1` (`ParentID`,`IndicatorType`,`IsDeleted`,`Active`)
) ENGINE=InnoDB AUTO_INCREMENT=10396 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ParentID` int(11) DEFAULT NULL,
  `LocationName` varchar(100) CHARACTER SET utf8 NOT NULL,
  `Active` tinyint(1) NOT NULL DEFAULT '1',
  `CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ModifiedDate` datetime DEFAULT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  `RegionID` int(11) DEFAULT NULL,
  `IsRegionComingFromParent` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Index1` (`id`,`ParentID`,`Active`,`IsDeleted`,`RegionID`)
) ENGINE=InnoDB AUTO_INCREMENT=445 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `approval_request_approvers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ApprovalRequestID` int(11) NOT NULL,
  `Approver` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ApproverMessage` varchar(500) CHARACTER SET utf8 DEFAULT NULL,
  `ApprovalDate` datetime DEFAULT NULL,
  `ApprovalStatus` int(11) DEFAULT NULL,
  `Level` int(11) DEFAULT NULL,
  `Sequence` int(11) DEFAULT NULL,
  `CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ModifiedDate` datetime DEFAULT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Index1` (`ApprovalRequestID`,`ModifiedDate`,`IsDeleted`,`ApprovalStatus`,`CreatedDate`,`ApprovalDate`)
) ENGINE=InnoDB AUTO_INCREMENT=1385 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `approval_request_tasks` (
  `ApprovalRequestID` int(11) NOT NULL,
  `TaskID` int(11) NOT NULL,
  `CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ModifiedDate` datetime DEFAULT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  KEY `Index1` (`ApprovalRequestID`,`TaskID`,`IsDeleted`,`ModifiedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tasks_details` (
  `TaskID` int(11) NOT NULL,
  `ObjectID` int(11) NOT NULL,
  `ObjectType` varchar(50) CHARACTER SET utf8 NOT NULL,
  `CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ModifiedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  KEY `Index1` (`TaskID`,`ObjectID`,`ObjectType`,`IsDeleted`,`ModifiedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `labels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Content` varchar(4096) DEFAULT NULL,
  `LanguageCode` varchar(10) DEFAULT NULL,
  `ObjectID` int(11) DEFAULT NULL,
  `ObjectName` varchar(100) DEFAULT NULL,
  `ColumnName` varchar(100) DEFAULT NULL,
  `CreatedBy` varchar(100) DEFAULT NULL,
  `CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `ModifiedBy` varchar(100) DEFAULT NULL,
  `ModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  `SyncID` varchar(50) DEFAULT NULL,
  `LastSyncDate` datetime DEFAULT NULL,
  `BreakSync` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Index1` (`LanguageCode`,`ObjectID`,`ObjectName`,`ColumnName`,`IsDeleted`),
  KEY `index2` (`IsDeleted`),
  KEY `index3` (`LanguageCode`),
  KEY `index4` (`ObjectID`),
  KEY `index5` (`ObjectName`),
  KEY `index6` (`ColumnName`)
) ENGINE=InnoDB AUTO_INCREMENT=470129 DEFAULT CHARSET=utf8;
  • MySQL version? The table structure: Not all tables listed. – Akina Oct 4 '19 at 9:06

Your Answer

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

Browse other questions tagged or ask your own question.