My company went through a database upgrade recently, from Mysql5 to Mysql8, and some of our queries have slowed down massively. (Going from taking in the order of a few seconds, to timing out after 30mins).

My only thought is that something must be wrong with the database setup, but it would be nice if some people in the know could have a look at this query and see if it could be improved.

select sd.*,
        sd.transactionDate as transactionDate,
        r.id as resellerId,
        max(pa.id) as platformAccountId,
        op.paymentTypeId as paymentTypeId,
        u.shopuserId as shopuserId,
        sd.currencyId as currencyId /* EUR */,
        GROUP_CONCAT(distinct concat('D', op.id) SEPARATOR ',') as orderPositionIds
        from doppaymentdata sd
        join doppaymentaccount sd1
        on sd1.id = sd.dopPaymentAccountId
        join orders o
        on o.platformOrderId = sd.orderId
        join orderposition op
        on o.id = op.ordersId and op.platformOrderPositionId = sd.orderPositionId
        join platformaccount pa
        on pa.id = o.platformAccountId
        join reseller r
        on r.id = pa.resellerId
        join sapshopuser u
        on u.shopuserId = o.shopuserId
        left join sapsettlement setl
        on setl.settlementitemId = sd.id and setl.settlementsource = 'DOP'
        where (sd.transactionType in ('PAYMENT_SHIPPED_ORDER'))
        and sd.amount > 0
        and u.uuid = 'SUCCESS'
        and setl.id is null
        and r.id in (1,2,3,6)
        and sd.transactionDate > '2014-06-30 23:59:59'
        group by sd.id

Of note, the orders table has ~26,000,000 rows, orderposition has ~55,000,000 rows, sapshopuser has ~17,000,000 rows, and doppaymentdata has ~1,500,000 rows. The other tables have negligible data in them.

The tables are exact duplicates of each other, the only difference being that one lot are in a Mysql5 database, the other lot in a Mysql8 database.

Running EXPLAIN gives the following output, which as you can see are both completely different:

(1st image is Mysql5, 2nd image is Mysql8 - click to enlarge)

EXPLAIN for Mysql5 query

EXPLAIN for Mysql8 query

What can I do to speed up the Mysql8 query?!

  • the images are unreaable
    – nbk
    Aug 6 '21 at 14:54
  • when you get so long runtimes look first in the error log tosee if something is a miss, also check buffer sizes temp sizes and so on
    – nbk
    Aug 6 '21 at 14:57
  • Welcome to DBA.se - try to avoid posting screenshots unless absolutely necessary, instead copy the full plan text into your question. Also consider posting the table/index definitions.
    – bbaird
    Aug 6 '21 at 14:58
  • 1
    Hi, and welcome to dba.se! Can you use EXPLAIN ANALYZE for your version 8? If so, the output of that could also be helpful! Please read this - it explains why images should only be used as a last resort. Also, the output of SHOW CREATE TABLE blah\G for your tables would be useful! Aug 6 '21 at 15:22

Do you need sd1? Or op? It is not providing any data; there is only a JOIN:

join  doppaymentaccount sd1  ON sd1.id = sd.dopPaymentAccountId

join  orderposition op  ON o.id = op.ordersId
      and  op.platformOrderPositionId = sd.orderPositionId

The JOIN will be 'performed' to verify that a matching row exists. If you expect there to be such a row, then the lookup is a waste of time. (I cannot tell how much of the 10 minutes is due to this.) Even changing to LEFT JOIN, wherein it would be really useless, does not prevent work on the part of MySQL. (MariaDB seems to be smart enough to toss the LEFT JOIN when none of the columns are used.)

The GROUP BY sd.id "smells" like a query that could benefit from "turning inside out". It seems to be doing an "explode-implode" -- that is 'exploding' the number of rows with all those JOINs, then 'imploding' down to one row per id`.

The cure for that is (sorry, I don't have the details for your case):

    FROM ( SELECT sd.id FROM sd ...
            ((minimal number of JOINs and WHEREs, etc to find the ids)) ) AS x
    JOIN ((the other tables, plus possibly back to `sd` to get `sd.*`,
               plus the other tables as needed. ))
    -- and hopefully no GROUP BY

If you still have troubles after those 'fixes', I'll make a pass to check the indexes. (There are about 14 suggestions now; too many for me to work on if you will be changing the query.) Please provide SHOW CREATE TABLE for all 8 tables so I can see the indexes you have.

Your Answer

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

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