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

Databases
1

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:

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

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.


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

oddz, in your subquery, you forgot the alumni_id in the SELECT clause

4

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!