How can I optimize this query and support multiple SKUs?

Question

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';

Answer 1

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 

Answer 2

I'm assuming that product_inventories has a one-to-many relationship with both product_locks and orderlineitems, and that is the reason you are using the subqueries. In that case (and not knowing anything else about the schema), I believe this query should work.

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