MySQL – Optimize Slow COUNT in Subquery

countMySQLoptimizationsubquery

I have the following query that takes minutes to complete:

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

This is because of the nested SELECT COUNT subquery. Without it, it runs blazing fast.

I made sure to add indexes on all the columns used to perform the JOINS.

If I extract the COUNT subquery to its own query, it is also really fast:

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

I tried to remove the subquery and calling COUNT directly in the SELECT, but to get the same results as the original query, I need to add the AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL )) condition to the main query. By doing so, I am not sure that I will not affect the original results:

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

This is quite fast as well, and seems to return the same results as the original query but I am not certain about this.

So is there a way to get rid of this subquery, or at least to make it more performant?

This is the output of EXPLAIN EXTENDED for the original query
output of EXPLAIN EXTENDED for the original query

Thank you. I am not a DBA, I spent hours on this and feel a bit stuck here.

Best Answer

Thanks to @Akine I did this:

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
Related Question