CODEWITHSUNDEEP
sqlmysqldatabaseperformancewarehouse
s0joya
s0joya

Reputation: 1

Optimizing Subquery Performance for Calculating Aggregated Weight Column in MySQL

I have a SQL query with a subquery that calculates the aggregated weight based on certain conditions. However, I'm experiencing performance issues with this subquery, and I would like to optimize it for better performance

SELECT ....,
        CASE WHEN MIN(IFNULL(g.grossWeight, 0)) = 0 THEN 
        
        (
        SELECT CONCAT('( ', TRIM(SUM(aad.qty_each *
                        bs.grossWeight))                                                   + 0,
                        'g )') 
        FROM act_allocation_details aad 
        LEFT JOIN BAS_SKU bs
        ON
            aad.organizationId = bs.organizationId
            AND aad.sku        = bs.sku
            AND aad.customerId = bs.customerId
        WHERE aad.orderno = a.orderno
        GROUP BY a.orderNo 
        )
        
        ELSE CONCAT('( ', ROUND(g.grossWeight), 'g )') END AS WEIGHT,
        ...
FROM
        table a
LEFT JOIN table b
ON
        a.organizationId  = b.organizationId
        AND a.warehouseId = b.warehouseId
        AND a.orderNo     = b.orderNo
LEFT JOIN table_sku c
ON
        a.organizationId = c.organizationId
        AND a.sku        = c.sku
        AND a.customerId = c.customerId
LEFT JOIN DOC_ORDER_PACKING_SUMMARY g
ON
        a.organizationId    = g.organizationId
        AND a.warehouseId   = g.warehouseId
        AND a.orderNo       = g.orderNo
        AND a.pickToTraceId = g.traceId
WHERE
    ...
GROUP BY
        a.sku           ,
        a.organizationId,
        a.warehouseId

The actual query has multiple join with other tables, and a parameter-based where condition (user input)

I tried to do it without the subquery like this with much better performance:

CASE WHEN MIN(IFNULL(g.grossWeight, 0)) = 0 THEN CONCAT('( ', TRIM(SUM(a.qty_each *
                        c.grossWeight))                                                   + 0,
                        'g )') ELSE CONCAT('( ', ROUND(g.grossWeight), 'g )') END AS WEIGHT,

but the expected result isn't the same because the calculation will be done for each GROUPED a.sku, not based on the entire WEIGHT column query result. If possible, can it be done without a subquery?

Upvotes: 0

Views: 37

Answers (1)

Rick James
Rick James

Reputation: 142518

I need to see the WHERE clause. Meanwhile, some of these may help:

g:  INDEX(organizationId,  grossWeight, warehouseId, orderNo, traceId)
a:  INDEX(organizationId, sku, warehouseId)
b:  INDEX(organizationId,  warehouseId, orderNo)
c:  INDEX(organizationId,  sku, customerId)
aad:  INDEX(orderno, organizationId,  qty_each, sku, customerId)
bs:  INDEX(organizationId,  grossWeight, sku, customerId)
a:  INDEX(orderno, orderNo)

Upvotes: 0

Related Questions