From: Ananda Kumar
Date: March 17 2010 3:58am
Subject: Re: Need help with query optimization
List-Archive: http://lists.mysql.com/mysql/220968
Message-Id: <829b199c1003162058l41ecdc92w946e5e8896a9a51d@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=001636e0add89493480481f71f64

--001636e0add89493480481f71f64
Content-Type: text/plain; charset=ISO-8859-1

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=anandkl@stripped
>
>

--001636e0add89493480481f71f64--