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
