Need help with query optimization
am 16.03.2010 19:17:45 von Jesse
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 | | 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=gcdmg-mysql-2@m.gmane.org
Re: Need help with query optimization
am 17.03.2010 04:58:13 von Ananda Kumar
--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 wrote:
> 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 | | 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@gmail.com
>
>
--001636e0add89493480481f71f64--
Re: Need help with query optimization
am 17.03.2010 12:05:58 von John Daisley
--0016e6542d32545a290481fd196f
Content-Type: text/plain; charset=ISO-8859-1
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 , 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 =TAP.CurrentMemberID
JOIN Chapters C On C.ID =M.ChapterID
JOIN Schools S On S.ID =C.SchoolID
JOIN TorchAwardSelAct Pts ON Pts.AchievementID=TAP.ID
WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL
AND Pts.LocalApproveStatus='A'
GROUP BY 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 wrote:
> 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 | | 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=john.daisley@butterflysys tems.co.uk
>
>
--0016e6542d32545a290481fd196f--