Question 268711 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql in Query to join query Optimisation

Trying to debug a slow query:

SELECT IFNULL(SUM(bt.transactionQuantity * bpd.unitPrice), 0)
FROM batchTransaction bt
LEFT JOIN batch b ON b.id = bt.batchId
LEFT JOIN batchPricingDetails bpd ON bpd.id = b.batchPricingDetailsId
WHERE (bt.productId, bt.dateCreated) IN (
    SELECT productId, MAX(dateCreated)
    FROM batchTransaction
    WHERE organisationId = '714361434540086498'
    AND dateCreated < 1689877800000
    GROUP BY productId
);

Instead of using in query trying to do join but it is giving erroneous results by a margin of greater than 10X.

SELECT IFNULL(SUM(bt.transactionQuantity * bpd.unitPrice), 0)
FROM batchTransaction bt
JOIN (
    SELECT bt.productId, max(bt.dateCreated)
    FROM batchTransaction bt
    WHERE bt.organisationId = '714361434540086498' AND bt.dateCreated < 1689877800000
    GROUP BY bt.productId
) AS filtered_data ON bt.productId = filtered_data.productId
LEFT JOIN batch b ON b.id = bt.batchId
LEFT JOIN batchPricingDetails bpd ON bpd.id = b.batchPricingDetailsId;
Tags:

2

Answers


  1. 0

    You’re missing the date comparison in the ON clause.

    SELECT IFNULL(SUM(bt.transactionQuantity * bpd.unitPrice), 0)
FROM batchTransaction bt
JOIN (
    SELECT bt.productId, max(bt.dateCreated) AS maxDate
    FROM batchTransaction bt
    WHERE bt.organisationId = '714361434540086498' AND bt.dateCreated < 1689877800000
    GROUP BY bt.productId
) AS filtered_data ON bt.productId = filtered_data.productId AND bt.dateCreated = filtered_data.maxDate
LEFT JOIN batch b ON b.id = bt.batchId
LEFT JOIN batchPricingDetails bpd ON bpd.id = b.batchPricingDetailsId;
    Login or Signup to reply.
  2. 0

    • Depending on how old your MySQL is, this construct may be very poorly optimized; avoid it:

      WHERE (a,b) IN ...

    • Also, try to change this to a JOIN or EXISTS (as you did in second query):

      ... IN ( SELECT ... )

    • Index:

      batchTransaction:  INDEX(organisationId, productId, dateCreated)

    • "giving erroneous results by a margin of greater than 10X" — Eh? More specifics, please.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search