How to improve MySQL stored procedure run for 20 seconds

Question

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.

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

The Explain plan for the stored procedure is as follows:

+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
| 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                                  |
+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+

The table structure of usage

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;

The table structure of locations

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;

The table structure of indicators

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;

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.

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');

EDIT:

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;

user_1766831 · Answer

Change

WHEN ( SELECT COUNT(x) ... ) > 0

to

WHEN EXISTS( SELECT 1 ... )

Furthermore, change

CASE WHEN EXISTS (...)
     THEN (SELECT ... LIMIT 1)
     ELSE LocationName
     END

to simply

IFNULL( (SELECT ... LIMIT 1), LocationName)

I don't see the CREATE TABLE labels; it needs

INDEX(ObjectID, ObjectName, ColumnName, LanguageCode)  -- in any order

I'm not sure that convert(u.Month, char(2)) will work correctly for 1-digit months.

What values can be in u.Q1? I may have an optimization for that long mess of ANDs of ORs.

This:

    and (
            p_approvalStatus is null
            or
            ( big select ) = p_approvalStatus
        )

looks like a big part of the query (see the EXPLAIN). Suggest speeding up some calls by pulling the NULL test out:

BEGIN
    IF (p_approvalStatus IS NULL)
    THEN
        SELECT  ...
            FROM ...
            WHERE ...
              and  ( (p_biMonthly1 ... )
              -- (skipping the p_approvalStatus stuff)
    ELSE
        SELECT  ...
            FROM ...
            WHERE ...
              and  ( (p_biMonthly1 ... )
              AND  ( big select ) = p_approvalStatus   -- (no check for NULL)
    END IF
END

There may be a way to further rewrite the ELSE part as well.

If I were writing this in, say, PHP, I would "construct" the query in PHP, thereby eliminating all the checks for 'null' and perhaps all the 'ors' Then issue the much shorter and simpler query.

Please provide SHOW CREATE TABLE labels, I want to understand the '644'.

user_74195 · Answer

Your "OR" conditions are crazy. Even with the parenthesis as you have them. Example for quarters 1-4

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

IF you provide null for Q4 AND include 3 specific values for Q1, Q2, Q3 you will still be going through all the records. So ensure you have what you mean... Example, if you DO the above, would that mean you ONLY want data from quarters 1-3? IF so, you probably mean to have your condition as

where ( -- either EVERY Quarter is null ( p_q1 is null AND p_q2 is null AND p_q3 is null AND p_q4 is null ) OR -- or, the quarter must match the specific one(s) that WERE provided. -- here you could provide only Q1 and Q3 and only pull those 2 quarters ( u.Q1 = p_q1 or u.Q2 = p_q2 or u.Q3 = p_q3 or u.Q4 = p_q4 ) )

I have applied this same context for the criteria for HalfYear 1-2, FourMonths 1-3, Quarters 1-4 and BiMonthly 1-6

Your other top area criteria for the years/months, from/to dates are also horrendous and might need to rethink, or edit your post and reclarify what you are TRYING to get vs parsing all date for years and or month portions of date/time fields.

As for your Locations and Indicators from the Language table, I have changed these to pre-queries to get an ID and a given language or indicator respectively. So, get the language once for each and left-join them. Then applying COALESCE(), you either get the language specific (if it exists), OR the one from the indicator or location table as default.

Here is the final rewritten with above contexts included

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
      coalesce( LangLoc.LocationName, l.LocationName ) as LocationName,
      l.ParentID as LocationParentID, 
      l.Active as LocationActive, 
      l.RegionID, 
      coalesce( LangInd.IndicatorName, i.IndicatorName ) as IndicatorName,
      i.ParentID as IndicatorParentID, 
      i.Unit, 
      i.DecimalPlaces, 
      i.Active as IndicatorActive, 
      i.IndicatorType, 
      u.*
   from
      usage as u
         left join Indicators as i
            on u.IndicatorID = i.id
        left join
        ( select
                objectID, 
                MIN( content ) IndicatorName
             from 
                labels as lbl 
             where 
                    LanguageCode = p_language
                and ObjectName = 'indicators' 
                and ColumnName = 'IndicatorName'
             group by
                objectID ) LangInd
             ON u.IndicatorID = LangInd.ObjectID

        left join locations as l 
           on u.LocationID = l.id
        left join
        ( select
                objectID, 
                MIN( content ) LocationName
             from 
                labels as lbl 
             where 
                    LanguageCode = p_language
                and ObjectName = 'locations'
                and ColumnName = 'LocationName'
             group by
                objectID ) Lang
             ON l.id = Lang.ObjectID
   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 = 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 
           -- qualifying half-year criteria
           (
                 (     p_halfYear1 is null 
                   AND p_halfYear2 is null 
                 )
              OR
                 (     u.HalfYear1 = p_halfYear1
                    OR u.HalfYear2 = p_halfYear2
                 )
            )
        and
           -- qualifying months criteria
           (
                  (     p_fourMonths1 is null 
                    AND p_fourMonths2 is null 
                    AND p_fourMonths3 is null 
                  )
               OR 
                  (    u.FourMonths1 = p_fourMonths1
                    or u.FourMonths2 = p_fourMonths2
                    or u.FourMonths3 = p_fourMonths3 
                  )
           )
        and
           -- qualifying quarters criteria
           (
               -- either EVERY Quarter is null
               (     p_q1 is null 
                 AND p_q2 is null 
                 AND p_q3 is null 
                 AND p_q4 is null
               )
             OR
               -- or, the quarter must match the specific one(s) that WERE provided.
               -- here you could provide only Q1 and Q3 and only pull those 2 quarters
               (    u.Q1 = p_q1
                 or u.Q2 = p_q2
                 or u.Q3 = p_q3
                 or u.Q4 = p_q4 
               )
           )
        and
           -- qualifying Bi-Monthly criteria
           (
               (     p_biMonthly1 is null 
                 AND p_biMonthly2 is null 
                 AND p_biMonthly3 is null 
                 AND p_biMonthly4 is null 
                 AND p_biMonthly5 is null 
                 AND p_biMonthly6 is null 
               )
             OR
               (     u.BiMonthly1 = p_biMonthly1
                  or u.BiMonthly2 = p_biMonthly2
                  or u.BiMonthly3 = p_biMonthly3
                  or u.BiMonthly4 = p_biMonthly4
                  or u.BiMonthly5 = p_biMonthly5
                  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

How to improve MySQL stored procedure run for 20 seconds

2 Answers