Slow query joining orders and products

Question

The end result I am trying to achieve is an array containing the total ordered qty for each product. I have been successful but the query takes about 20 minutes to run and I'm wondering if there is anyone who may know of some way of improving this? The details and current query are as follows:

• All columns involved in a join or where clause are indexed.

• Tables have been optimized.

orders_table:

• 2 million rows

• duplicate product codes where more than one order has been made for the same product (these can be grouped by product_code and pack_size_ordering_unit)

• has product code and barcode

product_table:

• 300 thousand rows
• no duplicates
• has product code and barcode
• query should be run for products where the distribution route is not 'C'
• query can be run for any products within any specified categories

product_pack_sizes: - 2 million rows - each product can be ordered in various pack sizes (e.g. 1 store pack of 4 units, 2 pallets of 84 units ect.) - no duplicates only has barcode

SELECT product_table.ProductCode as product_code, SUM(grouped_store_orders_table.po_qty*product_pack_sizes.pack_size_qty) AS order_qty
FROM (SELECT orders_table.barcode, orders_table.po_qty, orders_table.po_ordering_unit
FROM orders_table 
GROUP BY orders_table.barcode, orders_table.po_ordering_unit) AS grouped_store_orders_table
LEFT JOIN product_table
ON product_table.barcode = grouped_store_orders_table.barcode
LEFT JOIN product_pack_sizes
ON grouped_store_orders_table.barcode = product_pack_sizes.barcode AND grouped_store_orders_table.po_ordering_unit = product_pack_sizes.unit_of_measure
WHERE product_table.Category IN (2,3,4,5) AND product_table.DistroRoute <> 'C'
GROUP BY product_table.ProductCode

Execution plan:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    36756   Using temporary; Using filesort
1   PRIMARY product_table   ref barcode,Category    barcode 4   grouped_store_orders_table.article  1   Using where
1   PRIMARY product_pack_sizes  ref article,unit_of_measure article 4   trends.product_table.barcode    3   
2   DERIVED orders_table    ALL NULL    NULL    NULL    NULL    2137680 Using temporary; Using filesort

Answer 1

First things first - if you're going to need to maintain SQL, it's best to make it easy to maintain first: start with proper indentation and vertical spacing:

SELECT 
     product_table.ProductCode as product_code
    ,SUM(grouped_store_orders_table.po_qty*product_pack_sizes.pack_size_qty) AS order_qty
FROM 
    (SELECT 
         orders_table.barcode
        ,orders_table.po_qty
        ,orders_table.po_ordering_unit
     FROM orders_table 
     GROUP BY 
         orders_table.barcode, 
         orders_table.po_ordering_unit) AS grouped_store_orders_table
LEFT JOIN product_table
    ON product_table.barcode = grouped_store_orders_table.barcode
LEFT JOIN product_pack_sizes
    ON grouped_store_orders_table.barcode = product_pack_sizes.barcode 
    AND grouped_store_orders_table.po_ordering_unit = product_pack_sizes.unit_of_measure
WHERE product_table.Category IN (2,3,4,5) 
    AND product_table.DistroRoute <> 'C'
GROUP BY 
    product_table.ProductCode;

Aliases should make your life easier - I like dropping the AS keyword since it's not really needed, and I like to keep my aliases short/concise - and don't use them where they're not needed.

You're performing these joins, and then filtering product categories with a where clause; I don't know your data, but you may be able to significantly reduce the amount of rows involved in the join by filtering products first:

LEFT JOIN (SELECT * FROM product_table 
           WHERE Category IN (2,3,4,5) AND DistroRoute <> 'C') products
    ON products.barcode = source.barcode