mysql - 查询是否可以改进，因为它会出现超时异常

Question

我如何改进我的查询，因为它会超时异常。有人可以帮助我改进查询...？？？

UPDATE pdtdeliveryTable p  SET p.OrderID = IFNULL
( 
   (SELECT OrderID from OrderTable o where o.DiagramID=p.DiagramID AND o.DeliveryDate=
   (
        Select min(o2.DeliveryDate) from OrderTable o2 where o2.DiagramId=o.DiagramID Order by o2.DeliveryDate
   )Limit 1),0
)
where p.OrderID=0;

Answer 1

试试这个:

UPDATE pdtdeliveryTable p  
LEFT OUTER JOIN (SELECT o.DiagramID, o.OrderID FROM OrderTable o 
                 INNER JOIN ( SELECT DiagramID, MIN(DeliveryDate) DeliveryDate 
                              FROM OrderTable GROUP BY DiagramID
                            ) AS A ON o.DiagramID = A.DiagramID AND o.DeliveryDate = A.DeliveryDate 
               ) AS A ON A.DiagramID = p.DiagramID
SET p.OrderID = IFNULL(A.OrderID, 0);

或

UPDATE pdtdeliveryTable p  
LEFT OUTER JOIN ( SELECT DiagramID, OrderID 
                  FROM (SELECT DiagramID, OrderID, DeliveryDate FROM OrderTable ORDER BY DiagramID, DeliveryDate) AS A 
                  GROUP BY DiagramID 
                ) AS A ON p.DiagramID = A.DiagramID 
SET p.OrderID = IFNULL(A.OrderID, 0);