Optimize query with multiple subquery joins

Question

I have a query that gets the product description from product table (1st select) then subtracts it to the following subquery statements to get the no. of stocks remaining:

-sum of each product bought in the inventory table (2nd)

-sum of each product sold in the sales_detail table (3rd)

-sum of each product transferred to another branch in the stock_transfer table (4th)

-sum of each product that got damaged in the damaged_product table (5th)

The problem is every time this query loads, it goes full search of all 4 tables to get the sum of the quantity columns. And as time goes by, more records are stored and the query will become slower. Any suggestions?

SELECT p.Id,p.Product_Name Product,p.Description, c.Category_Name Category,sc.Subcategory_Name Subcategory,s.Supplier_Name Supplier, p.Selling_Price `Unit Price`,i.Stocks,s.Sales, i.Stocks - IFNULL(s.Sales, 0) - IFNULL(t.Transfer, 0) - IFNULL(d.Damage, 0) AS Remaining

FROM (SELECT Id, Product_Name, Description, Selling_Price, Category_Id, Subcategory_Id, Supplier_Id       FROM product WHERE enable_flag = 1) p

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(quantity), 0) AS Stocks
                FROM inventory
                WHERE enable_flag = 1 GROUP BY product_id) i ON p.Id = i.product_id

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(quantity), 0) AS Sales
                FROM sales_detail
                WHERE enable_flag = 1 GROUP BY product_id) s USING(product_id)

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(transfer_quantity), 0) AS Transfer
                FROM stock_transfer
                WHERE enable_flag = 1 GROUP BY product_id) t USING(product_id)

LEFT OUTER JOIN(SELECT product_id, COALESCE(SUM(damaged_quantity), 0) AS Damage
                FROM damaged_product
                WHERE enable_flag = 1 GROUP BY product_id) d USING(product_id)

JOIN Category c ON p.Category_Id=c.Id

JOIN Subcategory sc ON p.Subcategory_Id=sc.Id

JOIN Supplier s ON p.Supplier_Id=s.Id;

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-08-03T13:23:42

The use of subqueries prevents the use of indexes, which can slow down a query. I would suggest a query such as this:

SELECT p.*, sc.Subcategory_Name as Subcategory, s.Supplier_Name as Supplier,
       p.Selling_Price as `Unit Price`,
       (SELECT COALESCE(SUM(quantity), 0) 
        FROM inventory i
        WHERE s.enable_flag = 1 AND s.product_id = p.product_id
       ) as stocks,
       (SELECT COALESCE(SUM(quantity), 0) 
        FROM sales_detail sd
        WHERE i.enable_flag = 1 AND i.product_id = p.product_id
       ) as sales,
       (SELECT COALESCE(SUM(tansferquantity), 0) 
        FROM stock_transfer st
        WHERE st.enable_flag = 1 AND st.product_id = p.product_id
       ) as transfers,
       (SELECT COALESCE(SUM(damaged_quantity), 0) 
        FROM damage d
        WHERE d.enable_flag = 1 AND d.product_id = p.product_id
       ) as damaged
FROM product p JOIN
     Category c
     ON p.Category_Id = c.Id JOIN
     Subcategory sc
     ON p.Subcategory_Id = sc.Id JOIN
     Supplier s
     ON p.Supplier_Id = s.Id
WHERE p.enable_flag = 1;

For performance, the underlying tables want indexes on (product_id, enable_flag, quantity). MySQL can use the index for aggregation in a correlated subquery.

I realize that you also have calculated values. You may need to do these calculations in an outer query, taking the hit of an additional materialization of the subquery.

Optimize query with multiple subquery joins

1 Answers