Mysql – How to optimize this query and support multiple SKUs

MySQLperformancequery-performancequery-refactor

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also.

SELECT 
    available - (
        SELECT COALESCE(sum(quantity), 0) 
        FROM product_locks 
        WHERE sku = 'sku1'
    ) - (
        SELECT COALESCE(sum(quantity), 0) 
        FROM 
            orderlineitems 
            INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id 
        WHERE 
            sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1'
    ) AS free, 
    available AS total, 
    sku, 
    on_hold 
FROM product_inventories 
WHERE sku = 'sku1' AND salesite_id = 'site_id_1';

Best Answer

I have refactored the query to use left joins and IF statement checks for the joins so that you do not have to use nested sub-queries. The group by for sku and site will give the totals by site if needed, but this can be changed to suit your needs.

I am not sure of how the responses_authnets table joins to the orderlineitems for the siteid so this may need additional clarification 

SELECT (pi.available - SUM(IF(ISNULL(pl.quantity), 0, pl.quantity)) /* Sums up only those quantities which are not null through the left join */
- SUM (IF(ISNULL(ol.quantity), 0, ol.quantity))) as free, //* SUM up only quantities in the order line items which are not null */
pi.available as total, 
pi.sku,
pi.on_hold
FROM product_inventories pi
LEFT JOIN product_locks pl ON pl.sku = pi.sku /* Left join since not all products in the inventory may have locks */
LEFT JOIN orderlineitems ol ON ol.sku = pi.sku 
LEFT JOIN responses_authnetsid ra ON ra.id = ol.response_id /* Not sure how the responses come up here, so this join is not clear* 
GROUP BY pi.sku, pi.salesite_id
Related Question