How to optimize this MySQL routines Using MySQL 5.7.22

Run pt-query-digest on slow query log

 # Query 6: 0.00 QPS, 0.00x concurrency, ID 0x5863FD9F47316C48AE1FBD931F05AA56 at byte 9577338
# Scores: V/M = 2.36
# Time range: 2018-10-13T05:18:14 to 2018-11-27T02:05:07
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      33
# Exec time      1    145s      1s     13s      4s      8s      3s      2s
# Lock time      2   432us       0   168us    13us   113us    41us       0
# Rows sent      0  14.72k       3     890  456.79  874.75  391.41  112.70
# Rows examine   2  38.76M 171.32k   1.42M   1.17M   1.39M 387.45k   1.20M
# Query size     0  30.27k     127   2.27k  939.21   1.53k  727.48   1.46k
# String:
# Hosts        13.186.31.88 (16/48%), HKGNB16120... (14/42%)... 2 more
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  ####


This is my routine code https://paste.fedoraproject.org/paste/gky7Ec-Ws4baikNp1oTjtw

I run the routine and here is the profiles

mysql&gt; show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query
                                                                                                                               |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------+
|        3 | 9.70020200 | select
                t.id, t.DelegationID
        , case when (select count(lbl.id) from `labels` as lbl where lbl.ObjectID = t.id and lbl.ObjectName = 'tasks' and lbl.ColumnName = 'TaskName' and lbl.LanguageCode = p_language and lbl.IsDelete
d = 0) &gt; 0 then
                        (select content from `labels` as lbl where lbl.Obje |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)


It has run for 10 seconds, how can I improve this routine?
select  ara.ApprovalStatus
    from  `approval_request_approvers` as ara
    where  ara.ApprovalRequestID = rt.ApprovalRequestID
      and  ara.IsDeleted = 0
    order by  ara.ModifiedDate desc
    limit  1


is executed in 7 places.  See if you can make it only once.

And it could use

INDEX(IsDeleted, ApprovalRequestID,  -- in either order for WHERE
      ModifiedDate,                  -- 3rd; for ORDER BY
      ApprovalStatus)                -- for "covering"


You have some LEFT JOINs.  Could the be changed to JOIN?

Change from

or
            (
                select count(*) 
                from ...
            ) &gt; 0


to

or EXISTS ( SELECT * from ... )


For this:

p_createdBy is null or p_createdBy = ''


Consider deciding on NULL or '', then sticking with it.  This gets rid of one OR.

Any place where you use OR, see if you can reformulate the query.

Testing against NULL always fails in inequalities, so

(p_createdDateFrom is not null and p_createdDateFrom &lt;= t.CreatedDate)


-->

p_createdDateFrom &lt;= t.CreatedDate


The main WHERE has at least 11 tests AND'd together.  Are all of them TRUE most of the time?  If not, then rearrange them so that the most likely to fail are first.

Rather than passing in NULL/blank for things you don't want tested, have your client software construct the query with just the things you do want tested.  Then we could discuss other options for indexes.

Please provide EXPLAIN SELECT ... to discuss further.