CODEWITHSUNDEEP
mysqlprofiling
George Leventakis
George Leventakis

Reputation: 1

Extremely slow SQL select query

The following query:

SELECT 
  `so`.*,
  IF(
    ISNULL(`ips`.`border`),
    `io`.`cdborder`,
    `ips`.`border`
  ) AS order_status,
  IF(
    ISNULL(`ips`.`status`),
    `io`.`cdstatus`,
    `ips`.`status`
  ) AS order_state,
  `io`.*,
  IF(
    ISNULL(`ips`.`sale`),
    `iol`.`regelstatus`,
    `ips`.`sale`
  ) AS order_line_status 
FROM
  `sales_order` AS `so` 
  INNER JOIN `sales_flat_order_item` AS `soi` 
    ON soi.order_id = so.entity_id 
  LEFT JOIN `import`.`import_orders` AS `io` 
    ON so.atorder_id = io.cdorder 
    AND so.cdadmin = io.cdadmin 
    AND (error_msg IS NULL 
      OR error_msg = "") 
  LEFT JOIN `import`.`import_orderlines` AS `iol` 
    ON iol.cdorder = so.atorder_id 
    AND iol.cdadmin = so.cdadmin 
  LEFT JOIN `import`.`import_purchase_sales` AS `ips` 
    ON so.atorder_id = ips.order 
WHERE (soi.sku IS NULL) 
  OR (
    soi.sku = iol.cdproduct 
    AND (
      soi.atorder_line = iol.nrordrgl
    ) 
    AND (
      iol.atg != soi.qty_shipped 
      OR iol.at != soi.qty_invoiced 
      OR iol.atb != soi.qty_ordered
    )
  ) 
GROUP BY `so`.`atorder_id`,
  `so`.`cdadmin` 
ORDER BY `io`.`modification_date_order` ASC 
LIMIT 200

Is taking 4 minutes to execute! How can this be? Explain shows the following:

id     select_type     table     type         possible_keys         key         key_len     ref                                         rows     Extra
1      SIMPLE          so        index        PRIMARY               order_id    32          NULL                                        127828   Using temporary; Using filesort
1      SIMPLE          io        eq_ref       PRIMARY,error_msg     PRIMARY     261         livetest3.so.order_id,livetest3.so.cdadmin  1     
1      SIMPLE          soi       ref          IDX_ORDER             IDX_ORDER   4           livetest3.so.entity_id                      2             
1      SIMPLE          iol       ref          cdorder               cdorder     258         livetest3.so.order_id                       6        Using where
1      SIMPLE          ips       ref          sale_order            sale_order  32          livetest3.so.order_id                       3

I tried multiple query profiler tools but none show me detailed information about which part of the query is so slow...

The 'so' table only contains 130k rows... Even with a bunch of left joins it should not be this slow... Any ideas?

Upvotes: 0

Views: 161

Answers (4)

DRapp
DRapp

Reputation: 48169

Without knowing exactly what you WANT out of the data, your query has an instance of "soi.sku IS NULL" which would imply a LEFT JOIN to that alias. You had a normal join indicating you ALWAYS wanted a record match to "soi" Did you mean to take that to a LEFT JOIN instead? If you ALWAYS want the "soi", then I would remove the where "soi.sku IS NULL OR" portion of the query.

Other than that, the query itself looks ok, however, I would ensure you explicitly have the following indexes.

Table                         Index
Sales_Order                   (atorder_id, ccadmin)
import.import_orderlines      (cdorder, cdadmin)
import.import_orders          (cdorder, cdadmin)
import.import_purchase_sales  (order)

Then, change 

SELECT ...

to

SELECT STRAIGHT_JOIN ...

Upvotes: 1

0xCAFEBABE
0xCAFEBABE

Reputation: 5666

The query has to retrieve all 127828 records from the "so" table, put them into a temporary table, then execute all the IFs for each record, then sort after the fields you grouped by. Depending on the speed of the machine, it's load and especially the amount of available memory this execution time is not unrealistic.

Upvotes: 1

Icarus
Icarus

Reputation: 63962

It seems that the primary key is order_id but your query can't use it at all. If you create an index on sku your query should run faster.

Note that there's room for more improvement but I'd start by creating an index on sku and re-run the query again to see how it performs now.

Upvotes: 1

Chronial
Chronial

Reputation: 70803

The EXPLAIN gave you all the required information. The Using temporary; Using filesort part is the slow part.

Upvotes: 1

Related Questions