- 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
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
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
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.
a.alumni_id = c.alumni_id
a.alumni_id = c2.alumni_id
a.alumni_hold_code IS NULL
c.contribution_date BETWEEN '2002-10-10' AND '2003-10-10'
contrib_range_total BETWEEN 2000 AND 3000
oddz, in your subquery, you forgot the alumni_id in the SELECT clause
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!