以下查询：
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
需要4分钟才能执行！怎么会这样？说明显示以下内容：
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
我尝试了多种查询探查器工具，但没有一个向我显示有关查询哪一部分太慢的详细信息...
“ so”表仅包含13万行...即使有很多左联接，它也不应该这么慢...有什么想法吗？
最佳答案
EXPLAIN给您所有必需的信息。 Using temporary; Using filesort部分是较慢的部分。
关于mysql - 极慢的SQL选择查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8912325/