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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
