MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jesse Date:June 26 2006 8:28pm
Subject:Query Speed
View as plain text  
I have a query which I can execute in Microsoft SQL, and it's instantaneous. 
However, In MySQL, I've only been able to get it down to 48 seconds:

SELECT S.State, ST.StateName, S.Sub, C.ChapterType, (SELECT Count(*) FROM 
(Members M JOIN Chapters C1 ON C1.ID=M.ChapterID) JOIN Schools S1 on 
S1.ID=C1.SchoolID WHERE S1.State=S.State AND S1.Sub=S.Sub AND 
C1.ChapterType=C.ChapterType) AS TotMem, (SELECT Count(*) FROM 
((AdvChapterLink ACL JOIN LocalAdvisors LA ON LA.ID=ACL.AdvisorID) JOIN 
Chapters C2 ON C2.ID=ACL.ChapterID) JOIN Schools S2 ON S2.ID=C2.SchoolID 
WHERE S2.State=S.State AND S2.Sub=S.Sub AND C2.ChapterType=C.ChapterType AND 
LA.InvoiceNo IS NOT NULL) AS TotAdv
FROM (Chapters C JOIN Schools S ON S.ID=C.SchoolID)
    JOIN State ST ON S.State=ST.State
GROUP BY S.State, ST.StateName, S.Sub, C.ChapterType
ORDER BY S.State, S.Sub, C.ChapterType

I have added indexes to make sure that all of the JOINs and WHEREs can 
operate efficiently.  This helped tremendously, as my first attempt at this 
query timed out, so I have no idea how long it would have actually taken. 
I'm doing this query using ASP on a Windows XP Pro machine, however, doing 
it in the MySQL Query Browser takes just as long (as one would expect). 
The tables are all InnoDB.  Is there anything else I can do to help speed 
this query up?

Thanks,
Jesse 

Thread
Query SpeedJesse26 Jun
  • RE: Query SpeedRandall Price26 Jun
    • Re: Query SpeedJesse26 Jun
  • Re: Query SpeedDan Buettner26 Jun
  • Re: Query SpeedJesse27 Jun
    • Re: Query SpeedJay Pipes28 Jun
  • Re: Query SpeedJesse28 Jun
    • Re: Query SpeedJay Pipes28 Jun
Re: Query SpeedDan Buettner27 Jun
  • Re: Query SpeedDan Buettner27 Jun