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