Really slow query when ORDER BY added

Question

I have a fairly complex view that JOINs multiple tables and one VIEW which is simple but has to process millions of records:

SELECT wo.id_primary       AS id_primary, 
       wo.id_station       AS id_station, 
       wo.id_stage         AS id_stage, 
       wo.id_type          AS id_type, 
       wo.id_options       AS id_options, 
       wo.id_warehouse     AS id_warehouse, 
       wo.id_customer      AS id_customer, 
       wo.id_manufacturer  AS id_manufacturer, 
       wo.date_advanced    AS date_advanced, 
       wo.date_received    AS date_received, 
       wo.date_approved    AS date_approved, 
       wo.date_promised    AS date_promised, 
       wo.date_inspected   AS date_inspected, 
       wo.date_scheduled   AS date_scheduled, 
       wo.date_completed   AS date_completed, 
       sh.date_shipped     AS date_shipped, 
       wo.rpi_revision     AS rpi_revision, 
       wo.rpi_number       AS rpi_number, 
       wo.purchase_order   AS purchase_order, 
       wo.serial_number    AS serial_number, 
       wo.model_number     AS model_number, 
       wo.part_number      AS part_number, 
       wo.part_name        AS part_name, 
       wo.quantity         AS quantity, 
       wo.status           AS status, 
       wo.quote            AS quote, 
       wo.notes            AS notes, 
       sm.name             AS manufacturer_name, 
       wh.name             AS warehouse_name, 
       sc.name             AS customer_name, 
       ss.name             AS station_name, 
       sc.certs            AS customer_certifications, 
       sc.address1         AS customer_address, 
       sc.city             AS customer_city, 
       sc.email            AS customer_email, 
       sc.phone1           AS customer_phone, 
       sc.postal           AS customer_postal, 
       sc.fax              AS customer_fax, 
       sh.shipped          AS shipped, 
       sh.waybill          AS waybill, 
       sh.invoice          AS invoice, 
       wq.type_currency    AS type_currency, 
       wq.date_quoted      AS date_quoted, 
       wq.grand_total      AS grand_total, 
       Round(( ( sh.date_shipped - IF(( wo.date_approved = 
0 ), sh.date_shipped, wo.date_approved) ) / 
86400 ), 2) AS TAT, 
ws.stations         AS stations 
FROM   aerospace_erp.erp_workorder wo 
       LEFT JOIN aerospace_erp.system_manufacturers sm 
              ON wo.id_manufacturer = sm.id_primary 
       LEFT JOIN aerospace_erp.system_stations ss 
              ON wo.id_station = ss.id_primary 
       LEFT JOIN aerospace_erp.crm_customer sc 
              ON wo.id_customer = sc.id_primary 
       LEFT JOIN aerospace_erp.list_workorder_warehouse wh 
              ON wo.id_warehouse = wh.id_primary 
       LEFT JOIN aerospace_erp.erp_shipping sh 
              ON wo.id_primary = sh.id_workorder 
       LEFT JOIN aerospace_crm.crm_quoting wq 
              ON wo.id_primary = wq.id_workorder 
       LEFT JOIN aerospace_erp.erp_workorder_scope ws 
              ON wo.id_primary = ws.id_primary

This is in a VIEW as well. The erp_workorder_scope is the nested VIEW it's simple but takes a considerable time because of the amount of data it must process every time.

The whole VIEW above executes in about 15 seconds or about 1.5secs without the erp_workorder_scope JOIN

I can tolerate 15 seconds but when I add a ORDER BY xxx to the VIEW it literally runs for 45 mins.

This value will change based on what the user sorts on as well so I cannot hardcode this criteria in the VIEW itself.

Any ideas?

possible duplicate of [Slow query when using ORDER BY](http://stackoverflow.com/questions/884661/slow-query-when-using-order-by) — dkretz, Jan 04 '13 at 21:11
When you `ORDER BY xxx`, does `xxx` have an index on it? If not, that's your problem. — Eric Petroelje, Jan 04 '13 at 22:22

score 0 · Answer 1 · answered Jan 04 '13 at 22:33

try this trick

select * from (
SELECT wo.id_primary       AS id_primary, 
       wo.id_station       AS id_station, 
       wo.id_stage         AS id_stage, 
       wo.id_type          AS id_type, 
       wo.id_options       AS id_options, 
       wo.id_warehouse     AS id_warehouse, 
       wo.id_customer      AS id_customer, 
       wo.id_manufacturer  AS id_manufacturer, 
       wo.date_advanced    AS date_advanced, 
       wo.date_received    AS date_received, 
       wo.date_approved    AS date_approved, 
       wo.date_promised    AS date_promised, 
       wo.date_inspected   AS date_inspected, 
       wo.date_scheduled   AS date_scheduled, 
       wo.date_completed   AS date_completed, 
       sh.date_shipped     AS date_shipped, 
       wo.rpi_revision     AS rpi_revision, 
       wo.rpi_number       AS rpi_number, 
       wo.purchase_order   AS purchase_order, 
       wo.serial_number    AS serial_number, 
       wo.model_number     AS model_number, 
       wo.part_number      AS part_number, 
       wo.part_name        AS part_name, 
       wo.quantity         AS quantity, 
       wo.status           AS status, 
       wo.quote            AS quote, 
       wo.notes            AS notes, 
       sm.name             AS manufacturer_name, 
       wh.name             AS warehouse_name, 
       sc.name             AS customer_name, 
       ss.name             AS station_name, 
       sc.certs            AS customer_certifications, 
       sc.address1         AS customer_address, 
       sc.city             AS customer_city, 
       sc.email            AS customer_email, 
       sc.phone1           AS customer_phone, 
       sc.postal           AS customer_postal, 
       sc.fax              AS customer_fax, 
       sh.shipped          AS shipped, 
       sh.waybill          AS waybill, 
       sh.invoice          AS invoice, 
       wq.type_currency    AS type_currency, 
       wq.date_quoted      AS date_quoted, 
       wq.grand_total      AS grand_total, 
       Round(( ( sh.date_shipped - IF(( wo.date_approved = 
0 ), sh.date_shipped, wo.date_approved) ) / 
86400 ), 2) AS TAT, 
ws.stations         AS stations 
FROM   aerospace_erp.erp_workorder wo 
       LEFT JOIN aerospace_erp.system_manufacturers sm 
              ON wo.id_manufacturer = sm.id_primary 
       LEFT JOIN aerospace_erp.system_stations ss 
              ON wo.id_station = ss.id_primary 
       LEFT JOIN aerospace_erp.crm_customer sc 
              ON wo.id_customer = sc.id_primary 
       LEFT JOIN aerospace_erp.list_workorder_warehouse wh 
              ON wo.id_warehouse = wh.id_primary 
       LEFT JOIN aerospace_erp.erp_shipping sh 
              ON wo.id_primary = sh.id_workorder 
       LEFT JOIN aerospace_crm.crm_quoting wq 
              ON wo.id_primary = wq.id_workorder 
       LEFT JOIN aerospace_erp.erp_workorder_scope ws 
              ON wo.id_primary = ws.id_primary 
)
order by xxx

Really slow query when ORDER BY added

1 Answers1