Simple query with left join cause ndb cluster timeout, but work fine on innodb

Question

Recently I just move my database to ndb cluster, and I got database with around 200.000 record

I try to run this query to get monthly report, but always got query timeout in ndb cluster, when I try the query on the InnoDB it give fast query result < 1 Sec this the query:

 SELECT
    m.merchant_name,
    COUNT(CASE WHEN pay.payment_status = '00' THEN 1 ELSE NULL END) AS 'Success',
    SUM(CASE WHEN pay.payment_status = '00' THEN pay.transaction_amount ELSE 0 END) AS 'Total success',
    COUNT(CASE WHEN pay.payment_status != '00' THEN 1 ELSE NULL END) AS 'Fail',
    SUM(CASE WHEN pay.payment_status != '00' THEN pay.transaction_amount ELSE 0 END) AS 'Total Fail',
    COUNT(pay.trx_id) AS 'Total',
    SUM(pay.transaction_amount) AS 'Total Amount'
 FROM
   (
    SELECT
       mchn.merchant_name,
       mchl.channel_id
   FROM
      db_merchant mchn
   LEFT JOIN db_merchant_channel mchl ON
      mchl.merchant_id = mchn.merchant_id ) AS m
LEFT JOIN (
   SELECT
        x.trx_id,
        x.channel_id,
        fq.transaction_amount,
        fq.transaction_status,
        fs.payment_status
    FROM
       t_transaction x
    LEFT JOIN flag_request fq ON
       x.channel_id = fq.channel_id
       AND x.transaction_no = fq.transaction_no
    LEFT JOIN flag_response fs ON
       fs.freq_id = fq.freq_id

    WHERE
       LEFT(DATE(fs.created_at),7) = '2020-03' ) AS pay ON
   CONVERT(m.channel_id USING utf8) = CONVERT(pay.channel_id USING utf8)
GROUP BY
  m.channel_id

explain result from ndb cluster

explain result from innodb

what's possibly wrong?

score 0 · Answer 1 · answered May 09 '20 at 23:05

There are a lot of differences between NDB and InnoDB. Anyway, here are some things that make the queries inefficient:

This pattern is inefficient; see if you can reformulate it into something else:
```
FROM ( SELECT ... ) JOIN ( SELECT ... ) ON ...
```

Not sargable:

LEFT(DATE(fs.created_at),7) = '2020-03'

Change to

        fs.created_at >= '2020-03-01'
    AND fs.created_at  < '2020-03-01' + INTERVAL 1 MONTH

Not sargable:

ON CONVERT(m.channel_id USING utf8) = CONVERT(pay.channel_id USING utf8)

Instead, make sure both are declared to use the same CHARACTER SET and COLLATION and do simply

    ON m.channel_id = pay.channel_id

Please provide SHOW CREATE TABLE so we can discuss indexes.

Simple query with left join cause ndb cluster timeout, but work fine on innodb

1 Answers1