List:General Discussion« Previous MessageNext Message »
From:Jesse Date:March 16 2010 6:17pm
Subject:Need help with query optimization
View as plain text  
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


Thread
Need help with query optimizationJesse16 Mar
  • Re: Need help with query optimizationAnanda Kumar17 Mar
  • Re: Need help with query optimizationJohn Daisley17 Mar