Hi Jesse, I am not 100% sure cause I have only been using MySQL for ~6 months but I do read this mailing list everyday and have learned a lot. I believe that InnoDB tables to not maintain a count(*) for the tables so it has to physically count the rows. I believe MyISAM tables do maintain that count(*) so the tables were MyISAM they count(*) would be faster. That may be where the slowness is coming from.
Again, as I am new to MySQL, this may be totally off the wall. Maybe someone else more experienced with MySQL could verify this. Thanks, --Randall Price -----Original Message----- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Monday, June 26, 2006 4:28 PM To: MySQL List Subject: Query Speed 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
