Jesse wrote:
I worked with the query for a while, trying equi-joins instead of JOINs,
and variuos other things. I found that the queries that I was using to
represent the TotMem & TotAdv columns was what was closing things down.
I finally ended up using a sub-query to solve the problem. I gathered
the basic information, then added the "count columns" to that, and now,
I'm down to less than a second execution. Much better! Here's the new
query:
SELECT sq.State, sq.Sub, sq.StateName, sq.ChapterType, (SELECT Count(*)
FROM Members M JOIN Chapters C1 ON C1.ID=M.ChapterID JOIN Schools S1 ON
S1.ID=C1.SchoolID WHERE S1.State=sq.State AND S1.Sub=sq.Sub AND
C1.ChapterType=sq.ChapterType) AS TotMem, (SELECT Count(*) FROM
((AdvChapterLink ACL JOIN LocalAdvisors LA ON LA.ID=ACL.AdvisorID) JOIN
Chapters C2 ON C2.ID=ACL.ChapterID) JOIN Schools S2 ON S2.ID=C2.SchoolID
WHERE S2.State=Sq.State AND S2.Sub=Sq.Sub AND
C2.ChapterType=sq.ChapterType AND LA.InvoiceNo IS NOT NULL) AS TotAdv
FROM (SELECT S.State, S.Sub, ST.StateName, C.ChapterType FROM Schools S,
State ST, Chapters C WHERE ST.State=S.State AND C.SchoolID=S.ID GROUP BY
S.State, S.Sub, ST.StateName, C.ChapterType) AS sq ORDER BY State, Sub,
ChapterType
Correlated subqueries are evil. You will get much better performance by
converting the correlations to derived tables (subqueries in the FROM
clause) like so (I formatted it differently so I could read it).
Here is your original query (from above):
SELECT
sq.State
, sq.Sub
, sq.StateName
, sq.ChapterType
, (
SELECT Count(*)
FROM Members M
JOIN Chapters C1 ON C1.ID=M.ChapterID
JOIN Schools S1 ON S1.ID=C1.SchoolID
WHERE S1.State=sq.State
AND S1.Sub=sq.Sub
AND C1.ChapterType=sq.ChapterType
) AS TotMem
, (
SELECT Count(*)
FROM (
(
AdvChapterLink ACL
JOIN LocalAdvisors LA ON LA.ID=ACL.AdvisorID
)
JOIN Chapters C2 ON C2.ID=ACL.ChapterID
)
JOIN Schools S2 ON S2.ID=C2.SchoolID
WHERE S2.State=Sq.State
AND S2.Sub=Sq.Sub
AND C2.ChapterType=sq.ChapterType
AND LA.InvoiceNo IS NOT NULL
) AS TotAdv
FROM (
SELECT S.State, S.Sub, ST.StateName, C.ChapterType
FROM Schools S, State ST, Chapters C
WHERE ST.State=S.State
AND C.SchoolID=S.ID
GROUP BY S.State, S.Sub, ST.StateName, C.ChapterType
) AS sq
ORDER BY
State
, Sub
, ChapterType;
The problem is that the two correlated subqueries in the SELECT clause
will be executed *once for every result returned from the outer primary
query. That means lots of queries, needlessly. Instead, get rid of all
the parenthetical joins (not needed), the join to State in the GROUP BY
(not needed) and convert the correlations into derived tables, using
standard joins. Remember to think in terms of the *sets* of data upon
which you are operating, not in terms of iterating through a set of
data. They are different concepts. Here's the new query:
SELECT
primary_grouping.State
, primary_grouping.Sub
, ST.StateName
, primary_grouping.ChapterType
, member_counts.member_count AS TotMem
, advisor_counts.advisor_count AS TotAdv
FROM (
SELECT S.State, S.Sub, C.ChapterType
FROM Schools S
INNER JOIN Chapters C
ON S.ID = C.SchoolID
GROUP BY S.State, S.Sub, C.ChapterType
) AS primary_grouping
INNER JOIN (
SELECT S.State, S.Sub, C.ChapterType, COUNT(*) AS member_count
FROM Schools S
INNER JOIN Chapters C
ON S.ID = C.SchoolID
INNER JOIN Members M
ON C.ID = M.ChapterID
GROUP BY S.State, S.Sub, C.ChapterType
) AS member_counts
ON primary_grouping.State = member_counts.State
AND primary_grouping.Sub = member_counts.Sub
AND primary_grouping.ChapterType = member_counts.ChapterType
INNER JOIN (
SELECT S.State, S.Sub, C.ChapterType, COUNT(*) AS advisor_count
FROM Schools S
INNER JOIN Chapters C
ON S.ID = C.SchoolID
INNER JOIN AdvChapterLink ACL
ON C.ID = ACL.ChapterID
INNER JOIN LocalAdvisors LA
ON ACL.AdvisorID = LA.ID
AND LA.InvoiceNo IS NOT NULL
GROUP BY S.State, S.Sub, C.ChapterType
) AS advisor_counts
INNER JOIN State ST
ON primary_grouping.State = ST.State
ORDER BY
State
, Sub
, ChapterType;
This should reduce the number of queries actually executed to 3 instead
of > 700 million
Let us know the output of EXPLAIN and the speed difference.
Cheers,
--
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: [EMAIL PROTECTED] mob: +1 614 406 1267
Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]