Thanks for the help. Just so you know, I stated in the original message
that the tables are InnoDB, but I've since converted them to MyISAM to
see if that helped. It didn't. Here's the information you wanted:
Here are the table structures:
CREATE TABLE `members` (
`ID` int(10) NOT NULL,
`ChapterID` int(10) default NULL,
`FirstName` varchar(25) character set utf8 default NULL,
`MI` varchar(1) character set utf8 default NULL,
`LastName` varchar(25) character set utf8 default NULL,
`UID` varchar(15) character set utf8 default NULL,
`MemberType` varchar(20) character set utf8 default NULL,
`InvoiceNo` varchar(7) character set utf8 default NULL,
`PayDate` datetime default NULL,
`MembershipExpires` datetime default NULL,
`NLCEligible` tinyint(1) NOT NULL default '1',
`PayNatDues` tinyint(1) NOT NULL default '1',
`GPA` decimal(18,2) default NULL,
`GradYear` int(10) default NULL,
`Gender` varchar(1) character set utf8 default NULL,
`BusEdCourse` varchar(40) character set utf8 default NULL,
`AddDate` datetime default NULL,
`PhotoID` smallint(5) default NULL,
PRIMARY KEY (`ID`),
KEY `IX_Members` (`LastName`,`FirstName`,`MI`),
KEY `IX_Members_1` (`UID`),
KEY `IX_Members_2` (`InvoiceNo`),
KEY `IX_Members_3` (`ChapterID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `chapters` (
`ID` int(10) NOT NULL,
`SchoolID` int(10) default NULL,
`Name` varchar(50) character set utf8 default NULL,
`ChapterType` varchar(25) character set utf8 default NULL,
`UID` varchar(8) character set utf8 default NULL,
`NextMemNo` int(10) default NULL,
`Males` int(10) default NULL,
`Females` int(10) default NULL,
`AfricanAmerican` int(10) default NULL,
`Asian` int(10) default NULL,
`Caucasian` int(10) default NULL,
`Hispanic` int(10) default NULL,
`NativeAmerican` int(10) default NULL,
`Other` int(10) default NULL,
`Grade6` int(10) default NULL,
`Grade7` int(10) default NULL,
`Grade8` int(10) default NULL,
`Grade9` int(10) default NULL,
`Grade10` int(10) default NULL,
`Grade11` int(10) default NULL,
`Grade12` int(10) default NULL,
`Freshmen` int(10) default NULL,
`Sophomore` int(10) default NULL,
`Junior` int(10) default NULL,
`Senior` int(10) default NULL,
`PostGraduate` int(10) default NULL,
`AgeBelow22` int(10) default NULL,
`Age22_25` int(10) default NULL,
`Age26_30` int(10) default NULL,
`Age31_40` int(10) default NULL,
`AgeOver40` int(10) default NULL,
`Disabilities` int(10) default NULL,
`EducationallyDisabled` int(10) default NULL,
`EconomicallyDisadvantaged` int(10) default NULL,
`LimitedEnglishProficiency` int(10) default NULL,
`NonTraditional` int(10) default NULL,
`TempInvNo` varchar(7) character set utf8 default NULL,
`MatSentDate` datetime default NULL,
`TransferDate` datetime default NULL,
`AddDate` datetime default NULL,
`Reactivated` tinyint(1) default NULL,
`OverrideNLCHotel` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `IX_Chapters` (`UID`),
KEY `IX_Chapters_1` (`SchoolID`),
KEY `IX_Chapters_2` (`ChapterType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `schools` (
`ID` int(10) NOT NULL,
`Name` varchar(50) character set utf8 default NULL,
`Address` varchar(50) character set utf8 default NULL,
`City` varchar(30) character set utf8 default NULL,
`State` varchar(2) character set utf8 default NULL,
`Sub` varchar(2) character set utf8 default NULL,
`Zip` varchar(10) character set utf8 default NULL,
`BOContact` varchar(35) character set utf8 default NULL,
`BOAddress` varchar(40) character set utf8 default NULL,
`BOCity` varchar(30) character set utf8 default NULL,
`BOState` varchar(2) character set utf8 default NULL,
`BOZip` varchar(10) character set utf8 default NULL,
`BOPhone` varchar(13) character set utf8 default NULL,
`Phone` varchar(15) character set utf8 default NULL,
`Ext` varchar(10) character set utf8 default NULL,
`Fax` varchar(15) character set utf8 default NULL,
`Region` varchar(10) character set utf8 default NULL,
`District` varchar(10) character set utf8 default NULL,
`InvoiceRequired` tinyint(1) default '0',
`PrincipalsName` varchar(50) default NULL,
`PrincipalsEMail` varchar(65) default NULL,
PRIMARY KEY (`ID`),
KEY `IX_Schools` (`Name`),
KEY `IX_Schools1` (`State`,`Sub`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `advchapterlink` (
`ID` int(10) NOT NULL,
`AdvisorID` int(10) default NULL,
`ChapterID` int(10) default NULL,
`Rank` int(10) default NULL,
`Primary` tinyint(1) NOT NULL default '0',
`AllowToEdit` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`ID`),
KEY `IX_AdvChapterLink` (`AdvisorID`,`ChapterID`),
KEY `IX_AdvChapterLink_1` (`ChapterID`,`AdvisorID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `localadvisors` (
`ID` int(10) NOT NULL,
`FirstName` varchar(30) character set utf8 default NULL,
`LastName` varchar(35) character set utf8 default NULL,
`ChapterID` int(10) default NULL,
`Address` varchar(50) character set utf8 default NULL,
`City` varchar(30) character set utf8 default NULL,
`State` varchar(2) character set utf8 default NULL,
`Sub` varchar(2) character set utf8 default NULL,
`Zip` varchar(10) character set utf8 default NULL,
`EMailAddress` varchar(50) character set utf8 default NULL,
`HomePhone` varchar(15) character set utf8 default NULL,
`WorkPhone` varchar(15) character set utf8 default NULL,
`WorkExt` varchar(10) character set utf8 default NULL,
`CellPhone` varchar(15) character set utf8 default NULL,
`UserName` varchar(50) character set utf8 default NULL,
`FirstTimeIn` tinyint(1) NOT NULL default '1',
`Fax` varchar(15) character set utf8 default NULL,
`FaxPermission` tinyint(1) NOT NULL default '1',
`EMailPermission` tinyint(1) NOT NULL default '1',
`ContactType` varchar(20) character set utf8 default NULL,
`InvoiceNo` varchar(7) character set utf8 default NULL,
`PayDate` datetime default NULL,
`StartService` int(10) default NULL,
PRIMARY KEY (`ID`),
KEY `IX_LocalAdvisors` (`LastName`,`FirstName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
EXPLAIN returned several rows of information:
1, 'PRIMARY', 'ST', 'ALL', 'IX_State', '', '', '', 54, 'Using temporary;
Using filesort'
1, 'PRIMARY', 'S', 'ref', 'PRIMARY,IX_Schools1', 'IX_Schools1', '9',
'bpa.ST.State', 69, 'Using where'
1, 'PRIMARY', 'C', 'ref', 'IX_Chapters_1', 'IX_Chapters_1', '5',
'bpa.S.ID', 1, 'Using where'
3, 'DEPENDENT SUBQUERY', 'S2', 'ref', 'PRIMARY,IX_Schools1',
'IX_Schools1', '18', 'bpa.S.State,bpa.S.Sub', 65, 'Using where'
3, 'DEPENDENT SUBQUERY', 'C2', 'ref',
'PRIMARY,IX_Chapters_1,IX_Chapters_2', 'IX_Chapters_1', '5',
'bpa.S2.ID', 1, 'Using where'
3, 'DEPENDENT SUBQUERY', 'ACL', 'ref',
'IX_AdvChapterLink,IX_AdvChapterLink_1', 'IX_AdvChapterLink_1', '5',
'bpa.C2.ID', 2, 'Using where; Using index'
3, 'DEPENDENT SUBQUERY', 'LA', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'bpa.ACL.AdvisorID', 1, 'Using where'
2, 'DEPENDENT SUBQUERY', 'S1', 'ref', 'PRIMARY,IX_Schools1',
'IX_Schools1', '18', 'bpa.S.State,bpa.S.Sub', 65, 'Using where'
2, 'DEPENDENT SUBQUERY', 'C1', 'ref',
'PRIMARY,IX_Chapters_1,IX_Chapters_2', 'IX_Chapters_1', '5',
'bpa.S1.ID', 1, 'Using where'
2, 'DEPENDENT SUBQUERY', 'M', 'ref', 'IX_Members_3', 'IX_Members_3',
'5', 'bpa.C1.ID', 24, 'Using where; Using index'
Not sure what all that means, but there it is.
Now, as for the other stuff. I'm running MySQL version 5.0.15-nt. I'm
running on my development machine, which is a Dell Demension 3000. It's
running a Pentium 4 2.8 GHz CPU with 1 GB of RAM, with Windows XP Pro.
Thanks,
Jesse
----- Original Message ----- From: "Dan Buettner"
<[EMAIL PROTECTED]>
To: "Jesse" <[EMAIL PROTECTED]>
Cc: "MySQL List" <mysql@lists.mysql.com>
Sent: Monday, June 26, 2006 5:21 PM
Subject: Re: Query Speed
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
