I need some help optimizing the following query
SELECT sql_calc_found_rows ob.order_batch_idnum,
oi.order_idnum,
ost.order_stock_type_idnum,
oag.order_activity_group_idnum,
ob.request_contact_idnum,
oag.order_group_idnum,
oi.attention,
oi.customer_name,
oi.address_city,
oi.state_code,
DATE_FORMAT(oi.ship_by_date,‘%m/%d/%Y’) AS ship_by_date,
CONCAT_WS(’ ‘,rcon.name_first,rcon.name_last) AS requested_by,
CONCAT_WS(’ ‘,mcon.name_first,mcon.name_last) AS placed_by,
IF(ost.print_pick_ticket_date > ‘0000-00-00 00:00:00’,
DATE_FORMAT(ost.print_pick_ticket_date,’%m/%d/%Y’),
‘none’) AS print_pick_ticket_date,
DATE_FORMAT(ob.order_date,‘%m/%d/%Y’) AS order_date,
DATE_FORMAT(t.ship_date,‘%m/%d/%Y’) AS ship_date,
DATE_FORMAT(ost.invoice_date,‘%m/%d/%Y’) AS invoice_date,
IF(ost.deliver_date,DATE_FORMAT(ost.deliver_date,‘%m/%d/%Y’) ,
IF(oag.deliver_date,DATE_FORMAT(oag.deliver_date,‘%m/%d/%Y’) ,
‘’)) AS deliver_date,
sv.status_name,
ost.current_status,
oa.master_customer_idnum,
pst.product_stock_type,
pl.plant_name,
(SELECT SUM(oa_count.quantity_requested)
FROM order_activity oa_count
WHERE oa_count.order_stock_type_idnum = oa.order_stock_type_idnum
GROUP BY oa_count.order_stock_type_idnum) AS qty_total,
CONCAT(IF(NOT ISNULL(oi.address_street1),CONCAT(oi.address_street1,’ ‘),
‘’),IF(NOT ISNULL(oi.address_street2),CONCAT(oi.address_street2,’
‘),
‘’),IF(NOT ISNULL(oi.address_city),CONCAT(’‘,oi.address_city),
‘’),IF(NOT ISNULL(oi.country_division_idnum)
AND oi.country_division_idnum != 0,CONCAT(’, ‘,cd.division_code),
‘’),IF(NOT ISNULL(oi.address_zip5),CONCAT(’ ‘,oi.address_zip5),
‘’),IF(NOT ISNULL(oi.country_idnum)
AND oi.country_idnum != 0,CONCAT(’
‘,c.country),
‘’)) AS ship_address,
IF(MAX(os.time_stamp) > ‘0’,DATE_FORMAT(MAX(os.time_stamp),’%m/%d/%Y’),
‘none’) AS approved_date,
pur.purpose_name,
CONCAT(pro.promotion_name,‘, ‘,pta.promotion_activity_name) AS promotion_name,
cus.customer_name AS client_name,
oi.country_idnum,
oi.address_zip5,
sp.carrier_service_idnum,
IF(ost.deliver_date,ost.deliver_date,IF(oag.deliver_date,oag .deliver_date,’’)) AS need_date_timestamp,
lb.label_name,
dist.customer_name AS dist_of_record,
oag.order_comments,
t.order_id,
sp.ship_priority_idnum,
(SELECT COUNT(t_count.tracking_idnum) AS tracking_count
FROM shipping.tracking t_count
WHERE t_count.order_stock_type_idnum = ost.order_stock_type_idnum
AND t_count.void != 1
GROUP BY t_count.order_stock_type_idnum) AS tracking_summary,
oi.address_zip5,
oi.country_idnum,
sp.ship_priority_idnum,
sp.carrier_service_idnum,
IF(ost.deliver_date,ost.deliver_date,IF(oag.deliver_date,oag .deliver_date,‘’)) AS need_date_timestamp,
pst.food_stock_type_idnum,
ot.operation_type,
cus_usr.customer_idnum
FROM order_stock_type ost
LEFT JOIN order_activity oa
ON (oa.order_stock_type_idnum = ost.order_stock_type_idnum)
AND (oa.kit_master_idnum = 0)
LEFT JOIN order_info oi
ON oi.order_idnum = ost.order_idnum
LEFT JOIN order_batch ob
ON ob.order_batch_idnum = ost.order_batch_idnum
LEFT JOIN order_activity_group oag
ON oag.order_activity_group_idnum = ost.order_activity_group_idnum
LEFT JOIN order_status os
ON oa.order_activity_idnum = os.order_activity_idnum
LEFT JOIN product_stock_type pst
ON ost.product_stock_type_idnum = pst.product_stock_type_idnum
LEFT JOIN status_value sv
ON sv.status_idnum = ost.current_status
LEFT JOIN shipping.tracking t
ON t.order_stock_type_idnum = ost.order_stock_type_idnum
LEFT JOIN product p
ON p.product_idnum = oa.product_idnum
LEFT JOIN contact rcon
ON rcon.contact_idnum = ob.request_contact_idnum
LEFT JOIN contact mcon
ON mcon.contact_idnum = ob.master_contact_idnum
LEFT JOIN general.country_division cd
ON cd.country_division_idnum = oi.country_division_idnum
LEFT JOIN general.country c
ON c.country_idnum = oi.country_idnum
LEFT JOIN plant_location pl
ON oag.plant_location_idnum = pl.plant_location_idnum
LEFT JOIN purpose pur
ON pur.purpose_idnum = ob.purpose_idnum
LEFT JOIN promotion.promotion_to_activity pta
ON pta.promotion_to_activity_idnum = ob.promotion_to_activity_idnum
LEFT JOIN promotion.promotion pro
ON pro.promotion_idnum = pta.promotion_idnum
LEFT JOIN customer cus_usr
ON cus_usr.customer_idnum = ob.request_customer_idnum
LEFT JOIN customer cus
ON oa.master_customer_idnum = cus.customer_idnum
LEFT JOIN operation_type ot
ON ot.operation_type_idnum = cus_usr.operation_type_idnum
LEFT JOIN ship_priority sp
ON oag.ship_priority_idnum = sp.ship_priority_idnum
LEFT JOIN customer_stock_type cst
ON (cst.master_customer_idnum = ost.master_customer_idnum
AND cst.stock_type_idnum = ost.product_stock_type_idnum)
LEFT JOIN accounting.internal_account aia
ON aia.internal_account_idnum = cst.proteus_internal_account_idnum
LEFT JOIN label lb
ON lb.label_idnum = ob.label_idnum
LEFT JOIN customer dist
ON dist.customer_idnum = oi.distributor_idnum
WHERE ost.order_stock_type_idnum BETWEEN 162760
AND 218053
AND DATE_FORMAT(ob.order_date,‘%Y%m%d’) = ‘20070425’
AND os.status_idnum IN (3,41)
GROUP BY ost.order_stock_type_idnum
ORDER BY ost.order_stock_type_idnum
LIMIT 0,100
I ran an explain on it and got the following result
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY os range status_idnum,order_activity_idnum status_idnum 4 705961 Using where; Using temporary; Using filesort
1 PRIMARY oa eq_ref PRIMARY,order_stock_type_idnum,kit_master_idnum PRIMARY 4 shopping.os.order_activity_idnum 1 Using where
1 PRIMARY ost eq_ref PRIMARY,order_batch_idnum PRIMARY 4 shopping.oa.order_stock_type_idnum 1
1 PRIMARY ob eq_ref PRIMARY PRIMARY 4 shopping.ost.order_batch_idnum 1 Using where
1 PRIMARY oi eq_ref PRIMARY PRIMARY 4 shopping.ost.order_idnum 1
1 PRIMARY pst eq_ref PRIMARY PRIMARY 8 shopping.ost.product_stock_type_idnum 1
1 PRIMARY oag eq_ref PRIMARY PRIMARY 4 shopping.ost.order_activity_group_idnum 1
1 PRIMARY sv eq_ref PRIMARY PRIMARY 4 shopping.ost.current_status 1
1 PRIMARY t ref order_stock_type_idnum order_stock_type_idnum 4 shopping.oa.order_stock_type_idnum 2
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 shopping.oa.product_idnum 1 Using index
1 PRIMARY rcon eq_ref PRIMARY PRIMARY 4 shopping.ob.request_contact_idnum 1
1 PRIMARY mcon eq_ref PRIMARY PRIMARY 4 shopping.ob.master_contact_idnum 1
1 PRIMARY cd eq_ref PRIMARY PRIMARY 8 shopping.oi.country_division_idnum 1
1 PRIMARY c eq_ref PRIMARY PRIMARY 8 shopping.oi.country_idnum 1
1 PRIMARY pl eq_ref PRIMARY PRIMARY 8 shopping.oag.plant_location_idnum 1
1 PRIMARY pur eq_ref PRIMARY PRIMARY 4 shopping.ob.purpose_idnum 1
1 PRIMARY pta eq_ref PRIMARY PRIMARY 8 shopping.ob.promotion_to_activity_idnum 1
1 PRIMARY pro eq_ref PRIMARY PRIMARY 8 promotion.pta.promotion_idnum 1
1 PRIMARY cus_usr eq_ref PRIMARY PRIMARY 4 shopping.ob.request_customer_idnum 1
1 PRIMARY cus eq_ref PRIMARY PRIMARY 4 shopping.oa.master_customer_idnum 1
1 PRIMARY ot eq_ref PRIMARY PRIMARY 8 shopping.cus_usr.operation_type_idnum 1
1 PRIMARY sp eq_ref PRIMARY PRIMARY 8 shopping.oag.ship_priority_idnum 1
1 PRIMARY cst ref stock_type_idnum,master_customer_idnum stock_type_idnum 9 shopping.ost.product_stock_type_idnum 4
1 PRIMARY aia eq_ref PRIMARY PRIMARY 8 shopping.cst.proteus_internal_account_idnum 1 Using index
1 PRIMARY lb eq_ref PRIMARY PRIMARY 4 shopping.ob.label_idnum 1
1 PRIMARY dist eq_ref PRIMARY PRIMARY 4 shopping.oi.distributor_idnum 1
3 DEPENDENT SUBQUERY t_count ref VOID,order_stock_type_idnum order_stock_type_idnum 4 shopping.ost.order_stock_type_idnum 2 Using where
2 DEPENDENT SUBQUERY oa_count ref order_stock_type_idnum order_stock_type_idnum 5 shopping.oa.order_stock_type_idnum 3 Using where
