Re: Need help with query optimization
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.IDhttp://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
Need help with query optimization
I have the following query: SELECT 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=TAP.CurrentMemberID JOIN Chapters C On C.ID=M.ChapterID JOIN Schools S On 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 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=arch...@jab.org
Re: Need help with query optimization
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.IDhttp://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
