0

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

  • need show create table {tablename} from the tables involved and explain {query} output. p_locationIDs is null or p_locationIDs = '' normalise what an empty item is and then avoid or conditions. Only use LEFT JOIN when you want the missing RHS. FIND_IN_SET is slow (list of doom). – danblack Nov 27 '18 at 3:45
  • Thanks @danblack. Can you tell more on p_locationIDs is null or p_locationIDs = '' normalise what an empty item is – want_to_be_calm Nov 27 '18 at 3:54
  • This expression on its own can't use an index. As such your application should decide if NULL means empty or "" means empty. Once that is normalized the query can eliminate a criteria. Which one you use isn't particularly important, just choose one. – danblack Nov 27 '18 at 3:56
1
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 ...
            ) > 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 <= t.CreatedDate)

-->

p_createdDateFrom <= 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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.