Jesse, can you post table structures ( SHOW CREATE TABLE tablename )
and the output you get from EXPLAIN followed by the query below?
Also what version of MySQL you're on, and high level details of the
hardware (RAM, disks, processors, OS).
That will all be helpful in trying to help you out here.
Dan
Jesse wrote:
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
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
