Query Efficiency (mySQL) (1 Viewer)

[namliam]

I have a question regarding query efficiency in a bigger database, in this case mySQL but could be Oracle or SQL Server or whatever.

I have a query which to me looks awfull....

Select Cust.CustID, Custname, ErrorNoIP.Errors ErrorNoIP, ErrorNoName.Errors ErrorNoName, ErrorNoX.Errors ErrorNoX, ErrorNoY.Errors ErrorNoY
From   ( select CustID, CustName from TableX) as Cust
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 10 Group by CustID) ErrorNoIP        on Cust.CustID = ErrorNoIP.CustID
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 20 Group by CustID) ErrorNoName      on Cust.CustID = ErrorNoName.CustID
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 32 Group by CustID) ErrorNoX         on Cust.CustID = ErrorNoX.CustID
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 25 Group by CustID) ErrorNoY         on Cust.CustID = ErrorNoY.CustID

Now this is a sample offcourse and only has 4 errors in reality there are 25 of these subqueries

In my brain this would seem more efficient:

         select CustID
              , CustName
              , sum(Case when ErrorID = 10 then 1 Else 0 End ) ErrorNoIP
              , sum(Case when ErrorID = 20 then 1 Else 0 End ) ErrorNoName
              , sum(Case when ErrorID = 32 then 1 Else 0 End ) ErrorNoX
              , sum(Case when ErrorID = 25 then 1 Else 0 End ) ErrorNoY
         from TableX
         where ErrorID in (10,20,25,32)
         Group by CustID, CustName

However the people that support this query are sure that the initial query is the way to go... despite its seeming inefficient way of doing things. Though they are unable/willing to share exactly why this is true.
Just for FYI the table contains A LOT of records, dont know how many but probably in excess of 100 million and growing.

So I am hoping to find someone that can enlighten me... Which query is more efficient and why?

 

[namliam]

Just hoping to catch people's brain on monday morning

 

[jdraw]

Have you tried your approach with some sample data? Seems to me it might be one of those situations where different people have different thoughts, but no one knows for sure. Do the test and you'll know (not be guessing).

Good luck.