Optimize slow COUNT in MySQL subquery

Question

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.

• Rick James: The EXPLAIN references _fi2, but that is not in any of the queries -- Please fix. Also, please provide SHOW CREATE TABLE.
• Hajopa: RickJames this is because it is contained in one of the _Vw tables that are actually views: pastebin.com/VQekr5xL

Answer 1

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