Query Efficiency (mySQL) (1 Viewer)

namliam

The Mailman - AWF VIP
Local time
Today, 06:14
Joined
Aug 11, 2003
Messages
11,680
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....
Code:
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:
Code:
         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

The Mailman - AWF VIP
Local time
Today, 06:14
Joined
Aug 11, 2003
Messages
11,680
Just hoping to catch people's brain on monday morning :)
 

jdraw

Super Moderator
Staff member
Local time
Today, 00:14
Joined
Jan 23, 2006
Messages
13,401
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.
 

Users who are viewing this thread

Top Bottom