title: How to improve MySQL stored procedure run for 20 seconds
body: <p>I have a stored procedure which will run for 20 seconds and make high cpu usage, I found it in the slow query log, this stored procedure always exist and will run for 15 to 30 seconds. I believe this stored procedure caused the high CPU usage.
  So I am  trying to use explain to get the query plan for the stored procedure. And I am not having much idea how I can improve this stored procedure. Please share some light on how I can improve the stored procedure.</p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` 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) &gt; 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) &gt; 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
  </code></pre>
  
  <p>The Explain plan for the stored procedure is as follows:</p>
  
  <pre><code>+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
  | id |    select_type     | TABLE |  TYPE  | possible_keys  |   KEY   | key_len |                ref                | ROWS  | Extra  |                                              |
  +----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
  |  1 | PRIMARY            | u     | ALL    | (NULL)         | (NULL)  | (NULL)  | (NULL)                            | 75095 |  10.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                                  |
  +----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
  </code></pre>
  
  <p>The table structure of <code>usage</code></p>
  
  <pre><code>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`)
  ) ENGINE=InnoDB AUTO_INCREMENT=79273 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>The table structure of <code>locations</code></p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The table structure of <code>indicators</code></p>
  
  <pre><code>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;
  </code></pre>
  
  <p>When I called the following stored procedure, the Rows_examined is 1133668, and it runs to 7 seconds, and I think this caused the heavy calculation of CPU.  </p>
  
  <pre><code>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,308,364,365,300,304,301,302,303,345,306,323,349,400,350,401,320,440,402,392,324,403,321,441,351,362,361,360,393,322,346,399,325,347,348,299,423,307,381,353,397,352,398,382,358,354,357,355,356,359,145,434,435,438,436,297,430,431,432,433,311,83,85,298,58,40,60,319,59,329,296,61,422,305,327,384,396,383,326,330,62,310,309,312,407', '10065', '2017-12-01', '2017-12-31', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 202, 'en-US');
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <pre><code>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;
  </code></pre>