優化 MySQL 子查詢中的慢 COUNT

我有以下查詢需要幾分鐘才能完成：

SELECT DISTINCT Designation, Model, (
   SELECT COUNT( Equipment.EquipmentID ) 
   FROM Equipment 
   INNER JOIN EquipmentDesignation_Vw ON Equipment.EquipmentID = EquipmentDesignation_Vw.EquipmentID 
   LEFT JOIN EquipmentIssuance_Vw eqpi on Equipment.EquipmentSerial = eqpi.EquipmentSerial 
   WHERE Equipment.ItemID = i.ItemID 
   AND EquipmentDesignation_Vw.Designation = ed.Designation 
       AND EquipmentDesignation_Vw.ArmoryID IN 2 
   AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL ))
) AS Total
FROM Item i 
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
LEFT JOIN Equipment e ON e.ItemID = i.ItemID
LEFT JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation = 'Issue'
   AND ed.ArmoryID IN 2   
   AND i.ItemCategoryID = 7

這是因為嵌套的 SELECT COUNT 子查詢。沒有它，它執行得非常快。

我確保在用於執行 JOINS 的所有列上添加索引。

如果我將 COUNT 子查詢提取到自己的查詢中，它也非常快：

SELECT i.ItemID, COUNT( Equipment.EquipmentID ) 
FROM Equipment 
INNER JOIN Item i on i.ItemID = Equipment.ItemID 
INNER JOIN EquipmentDesignation_Vw ON Equipment.EquipmentID = EquipmentDesignation_Vw.EquipmentID 
LEFT JOIN EquipmentIssuance_Vw eqpi on Equipment.EquipmentSerial = eqpi.EquipmentSerial 
WHERE Equipment.ItemID = i.ItemID AND EquipmentDesignation_Vw.Designation = 'Issue' 
AND EquipmentDesignation_Vw.ArmoryID IN 2 
AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL )) 
GROUP BY i.ItemID

我嘗試在 SELECT 中刪除子查詢並直接呼叫 COUNT，但要獲得與原始查詢相同的結果，我需要將AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL ))條件添加到主查詢中。通過這樣做，我不確定我是否會影響原始結果：

SELECT DISTINCT Designation, Model, COUNT( e.EquipmentID ) 
FROM Item i 
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
LEFT JOIN Equipment e ON e.ItemID = i.ItemID
LEFT JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation =  'Issue'
AND ed.ArmoryID  IN 2   
AND ((ei.IssueLocationID is NULL) OR (ei.IssueLocationID is not NULL AND ei.ReturnLocationID is not NULL ))
GROUP BY i.ItemID

這也相當快，並且似乎返回與原始查詢相同的結果，但我對此不確定。

那麼有沒有辦法擺脫這個子查詢，或者至少讓它更高效？

這是原始查詢的 EXPLAIN EXTENDED 的輸出

謝謝你。我不是 DBA，我在這上面花了幾個小時，感覺有點卡在這裡。

感謝@Akine，我做到了：

SELECT DISTINCT Designation, Model,
COUNT(CASE WHEN ((ei.IssueLocationID IS NULL) OR (ei.IssueLocationID IS NOT NULL AND ei.ReturnLocationID IS NOT NULL )) THEN e.EquipmentID ELSE NULL END) AS Total
FROM Item i
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
INNER JOIN Equipment e ON e.ItemID = i.ItemID
INNER JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation = 'Issue'
AND ed.ArmoryID  IN 2 
GROUP BY i.ItemID

引用自：https://dba.stackexchange.com/questions/246053