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

Reply via email to