Re: Query Speed

Wed, 28 Jun 2006 05:48:36 -0700 

Jesse wrote:
The error was, "Unknown column 'primary_grouping.State' in 'on clause'". I assume this is in the ON clause that's JOINing the member_counts to the primary_grouping. 

No, that's because of a stupid mistake on my part.  Here you go:

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 State ST
 ON primary_grouping.State = ST.State
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
 ON primary_grouping.State = advisor_counts.State
 AND primary_grouping.Sub = advisor_counts.Sub
 AND primary_grouping.ChapterType = advisor_counts.ChapterType;



----- Original Message ----- From: "Jay Pipes" <[EMAIL PROTECTED]>
To: "Jesse" <[EMAIL PROTECTED]>
Cc: "mysql" <mysql@lists.mysql.com>
Sent: Tuesday, June 27, 2006 7:22 PM
Subject: Re: Query Speed



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 





--
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]















    











					Reply via email to