It may only be returning 51 rows but its having to read significantly more.
Get rid of the derived table join if possible. Something like SELECT TAP.ID <http://tap.id/>, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Sum(Pts.Points) Total_Points FROM TorchAwardParticipants TAP JOIN Members M On M.ID <http://m.id/>=TAP.CurrentMemberID JOIN Chapters C On C.ID <http://c.id/>=M.ChapterID JOIN Schools S On S.ID <http://s.id/>=C.SchoolID JOIN TorchAwardSelAct Pts ON Pts.AchievementID=TAP.ID <http://tap.id/> WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL AND Pts.LocalApproveStatus='A' GROUP BY TAP.ID <http://tap.id/>, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District ORDER BY TAP.LastName, TAP.FirstName, Sum(Pts.Points) Regards John On Tue, Mar 16, 2010 at 6:17 PM, Jesse <j...@msdlg.com> wrote: > I have the following query: > > SELECT TAP.ID <http://tap.id/>, M.UID, TAP.FirstName, TAP.MI, > TAP.LastName, TAP.State, > TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, > S.Region, S.District,Pts.TotPoints > FROM TorchAwardParticipants TAP > JOIN Members M On M.ID <http://m.id/>=TAP.CurrentMemberID > JOIN Chapters C On C.ID <http://c.id/>=M.ChapterID > JOIN Schools S On S.ID <http://s.id/>=C.SchoolID > JOIN (SELECT AchievementID,Sum(Points) As TotPoints > FROM TorchAwardSelAct TASA > WHERE LocalApproveStatus='A' > GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.ID<http://tap.id/> > WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT > NULL > ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints > > The TorchAwardParticipants table has about 84,000 records in it. > The query takes almost 40 seconds to return the data, which is only 51 > rows. > An EXPLAIN returns the following: > > +----+-------------+------------+--------+-------------------------+---------------+---------+-------------------------+--------+---------------------------------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > > +----+-------------+------------+--------+-------------------------+---------------+---------+-------------------------+--------+---------------------------------+ > | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | > NULL | NULL | 4382 | Using temporary; Using filesort > | > | 1 | PRIMARY | TAP | eq_ref | PRIMARY,CurrentMemberID | > PRIMARY | 4 | Pts.AchievementID | 1 | Using where | > | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3 | > PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | > | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | > PRIMARY | 4 | bpa.M.ChapterID | 1 | | > | 1 | PRIMARY | S | eq_ref | PRIMARY | > PRIMARY | 4 | bpa.C.SchoolID | 1 | | > | 2 | DERIVED | TASA | index | NULL | > AchievementID | 5 | NULL | 161685 | Using where | > > +----+-------------+------------+--------+-------------------------+---------------+---------+-------------------------+--------+---------------------------------+ > > What is the best way to optimize this query so that it doesn't take 40 > seconds to return the dataset? > > Jesse > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk > >
