Can you please show us the indexes on both the tables. regards anandkl
On Tue, Mar 16, 2010 at 11:47 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=anan...@gmail.com > >
