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