SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    can i speed up this query?, two inline select sums

    The goal:

    • There are two tables, a table of alumni (~12,000 records) and a table of contributions (~10,000 records) - each contribution is related by the alumni's ID
    • Get a list of alumni (with HOLD being NULL) who have contributed 2000-3000 within the date range of 2002-10-10 and 2003-10-10.
    • Result needs to show alumni's name, ID, each alumni's total given for all-time, and each alumni's total given for the specified date range


    The attempt:
    Code MySQL:
    SELECT alumni.alumni_id, alumni.alumni_name,
    (SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id) AS contrib_alltime_total,
    (SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id AND contribution.contribution_date BETWEEN '2002-10-10' AND '2003-10-10') AS contrib_range_total
     
    FROM alumni
    WHERE alumni.hold_code IS NULL
     
    GROUP BY alumni.alumni_id
    HAVING contrib_range_total BETWEEN 2000 AND 3000

    The query works and gets the output I need, but takes 80-100 SECONDS to run! My users are not going to be that patient

    Not to mention that this is part of a PHP script that allows them to change the values of the ranges, so if they do this for a range of years it could be incredibly slow

    Is there any way I can speed up this query? I am assuming the inline SELECT SUMs are the culprit, but I need those values

    Thank you

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    untested, but if your table is properly indexed should yield a more optimized query. The sub-selects are probably what is killing your current solution.

    Code SQL:
    SELECT
          a.alumni_id
         ,a.alumni_name
         ,SUM(c.contributed_amt) contrib_range_total
         ,c2.contrib_alltime_total
      FROM
         alumni a
     INNER
      JOIN
         contribution c
        ON
         a.alumni_id = c.alumni_id
     INNER
      JOIN
         (SELECT
               SUM(c.contributed_amt) contrib_alltime_total
            FROM
               contribution c
           GROUP
              BY
               c.alumni_id) c2
        ON
         a.alumni_id = c2.alumni_id
     WHERE
         a.alumni_hold_code IS NULL
       AND
         c.contribution_date BETWEEN '2002-10-10' AND '2003-10-10'
     GROUP
        BY
         a.alumni_id
    HAVING 
         contrib_range_total BETWEEN 2000 AND 3000

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oddz, in your subquery, you forgot the alumni_id in the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    great, just added the alumni_id in the inner join select and its working super fast

    makes more sense than running two inline SUM queries for every record in the table!


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •