On the chance that such a thing were actually happening, I converted all of
my tables over to MyISAM, because it says that it is "very fast", and I'm
not using Transactions anyway. If that's the only feature that InnoDB gives
me, I'd much rather have the speed. Anyway, it actually increased the time
of this query from about 48 seconds to about 1:40 or so. More than twice as
much. However, I've noticed that other things do seem to be running faster
since converting to MyISAM, but this query is not.
Thanks,
Jesse
----- Original Message -----
From: "Price, Randall" <[EMAIL PROTECTED]>
To: "Jesse" <[EMAIL PROTECTED]>; "MySQL List" <mysql@lists.mysql.com>
Sent: Monday, June 26, 2006 4:47 PM
Subject: RE: Query Speed
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]
