SQL query taking too long to execute
I have below query taht takes around 10-15 min to execute even if the final output is 10records
Select etd,(inv_model_amt+inv_option_amt) amt,company_name,remarks,prod_month, company_id, model_code, status_ag, order_number, chassis_front, chassis_number, engine_number, package, colour_code, upholstery_code, options, warehouse, lot from orders_current left outer join (select company_id comp_id,company_name from auth_company) ac on (orders_current.company_id=ac.comp_id) left outer join (select order_number number,remarks from orders_remarks) orem on (orders_current.order_number=orem.number) left outer join (SELECT warehouse,lot,GROUP_CONCAT(permit_type ORDER BY permit_type) AS pr,order_number num FROM permit_orders GROUP BY order_number) op on (orders_current.order_number=op.num) left outer join (select shipment_id,order_number so_order_number from shipment_orders) so on (orders_current.order_number=so.so_order_number) left outer join (select shipment_id,eta,etd from shipment ) s on (so.shipment_id=s.shipment_id) where orders_current.order_number IS NOT NULL and orders_current.company_id = '1' and status_ag = '95'and (eta is NULL or eta ='') AND YEAR(delivery_date)>2011 group by orders_current.order_number;
Could someone help me to optimize thsi query.
If i check the process list it takes too much time on "copying data to temp table" and "sending data"