查出来了,问题出现在以下这句AND so.ID in (SELECT sellOrderID FROM wit_sell_order_product AS SOPtop WHERE warehouseID = 1 AND sellQuantity > (SELECT IFNULL(SUM(outWarehouseQuantity),0) FROM wit_out_warehouse_order_product WHERE ( sellOrderID = SOPtop.sellOrderID AND productID = SOPtop.productID AND sellOrderProductID = SOPtop.ID AND warehouseID = 1 ))) 但是还不知道该如何优化。
你这不是 EXPLAIN 的报告,无从说起
mysql优化,需要先分析sql,常见的有explain,descdesc SELECT so.ID,so.orderNum,so.sellTime,so.accountsReceivableWay,so.accountsReceivableWayID,so.customerName,so.customerAdd,so.customerTel,so.re,so.status,so.sellDepName,so.ISInternal,so.deliverDate,so.distributorID,so.CashOnDelivery,so.CashOnDeliveryAmount,so.isCloseout,so.isReplacement,so.totalAmount,so.receivedTheDeposit,dis.DistributorName,dis.principal,dis.address,dis.tel AS disTel,s.fullName AS sellStaffName,s.number AS sellStaffNum FROM wit_sell_order AS so LEFT JOIN wit_staff AS s ON s.ID = so.sellStaffID LEFT JOIN wit_distributor AS dis ON dis.ID = so.distributorID WHERE so.deliverDate <> 0 AND (status = 2 OR status = 3) AND so.letOUT = 1 AND so.ID in (SELECT sellOrderID FROM wit_sell_order_product AS SOPtop WHERE warehouseID = 1 AND sellQuantity > (SELECT IFNULL(SUM(outWarehouseQuantity),0) FROM wit_out_warehouse_order_product WHERE ( sellOrderID = SOPtop.sellOrderID AND productID = SOPtop.productID AND sellOrderProductID = SOPtop.ID AND warehouseID = 1 )) AND lastPrintInvoice is null) AND so.deliverDate < 1407427200 GROUP BY so.ID desc贴出结果看看就知道,哪些字段需要做优化处理。
帮你整理下代码吧,你这子查询也用得太多了. 不清楚你要做什么,感觉这代码可能需要另写. SELECT so.ID, so.orderNum, so.sellTime, so.accountsReceivableWay, so.accountsReceivableWayID , so.customerName, so.customerAdd, so.customerTel, so.re, so.status , so.sellDepName, so.ISInternal, so.deliverDate, so.distributorID, so.CashOnDelivery , so.CashOnDeliveryAmount, so.isCloseout, so.isReplacement, so.totalAmount, so.receivedTheDeposit , dis.DistributorName, dis.principal, dis.address, dis.tel AS disTel, s.fullName AS sellStaffName , s.number AS sellStaffNum FROM wit_sell_order so LEFT JOIN wit_staff s ON s.ID = so.sellStaffID LEFT JOIN wit_distributor dis ON dis.ID = so.distributorID WHERE so.deliverDate <> 0 AND (status = 2 OR status = 3) AND so.letOUT = 1 AND so.ID IN (SELECT sellOrderID FROM wit_sell_order_product SOPtop WHERE warehouseID = 1 AND sellQuantity > ( SELECT IFNULL(SUM(outWarehouseQuantity), 0) FROM wit_out_warehouse_order_product WHERE sellOrderID = SOPtop.sellOrderID AND productID = SOPtop.productID AND sellOrderProductID = SOPtop.ID AND warehouseID = 1 ) AND lastPrintInvoice IS NULL) AND so.deliverDate < 1407427200 GROUP BY so.ID DESC
不清楚你要做什么,感觉这代码可能需要另写.
SELECT so.ID, so.orderNum, so.sellTime, so.accountsReceivableWay, so.accountsReceivableWayID
, so.customerName, so.customerAdd, so.customerTel, so.re, so.status
, so.sellDepName, so.ISInternal, so.deliverDate, so.distributorID, so.CashOnDelivery
, so.CashOnDeliveryAmount, so.isCloseout, so.isReplacement, so.totalAmount, so.receivedTheDeposit
, dis.DistributorName, dis.principal, dis.address, dis.tel AS disTel, s.fullName AS sellStaffName
, s.number AS sellStaffNum
FROM wit_sell_order so
LEFT JOIN wit_staff s ON s.ID = so.sellStaffID
LEFT JOIN wit_distributor dis ON dis.ID = so.distributorID
WHERE so.deliverDate <> 0
AND (status = 2
OR status = 3)
AND so.letOUT = 1
AND so.ID IN (SELECT sellOrderID
FROM wit_sell_order_product SOPtop
WHERE warehouseID = 1
AND sellQuantity > (
SELECT IFNULL(SUM(outWarehouseQuantity), 0)
FROM wit_out_warehouse_order_product
WHERE sellOrderID = SOPtop.sellOrderID
AND productID = SOPtop.productID
AND sellOrderProductID = SOPtop.ID
AND warehouseID = 1
)
AND lastPrintInvoice IS NULL)
AND so.deliverDate < 1407427200
GROUP BY so.ID DESC