...

View Full Version : SQL query taking too long to execute



coolguyraj
03-11-2013, 09:56 AM
Hi,

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"

Thanks

Old Pedant
03-11-2013, 08:04 PM
What do you see when you ask MySQL to EXPLAIN that query?

coolguyraj
03-13-2013, 08:02 AM
The output of EXPLAIN is as follows:



+----+-------------+-----------------+-------+-----------------------+---------+---------+-------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+-----------------------+---------+---------+-------+-------+----------------------------------------------+
| 1 | PRIMARY | orders_current | ref | PRIMARY,idex_4,idex_6 | idex_4 | 1 | const | 33198 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 19 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 7738 | |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 39729 | |
| 1 | PRIMARY | <derived5> | ALL | NULL | NULL | NULL | NULL | 49931 | |
| 1 | PRIMARY | <derived6> | ALL | NULL | NULL | NULL | NULL | 2247 | Using where |
| 6 | DERIVED | shipment | ALL | NULL | NULL | NULL | NULL | 2247 | |
| 5 | DERIVED | shipment_orders | index | NULL | PRIMARY | 24 | NULL | 49931 | Using index |
| 4 | DERIVED | permit_orders | ALL | NULL | NULL | NULL | NULL | 77637 | Using filesort |
| 3 | DERIVED | orders_remarks | ALL | NULL | NULL | NULL | NULL | 7738 | |
| 2 | DERIVED | auth_company | ALL | NULL | NULL | NULL | NULL | 19 | |
+----+-------------+-----------------+-------+-----------------------+---------+---------+-------+-------+----------------------------------------------+

Old Pedant
03-13-2013, 09:20 PM
Well, see all those lines where the type is "ALL"? That means MySQL is doing a full table scan in each case. And so of course it's slow.

Time to look into what indexes could help your cause. Note that you don't even have any *possible* keys for all of those.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum