List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:March 17 2010 11:05am
Subject:Re: Need help with query optimization
View as plain text  
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 <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
>
>

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