35 minutes ago, IncrediblePony said:
For full disclosure I come up with this query. It's ugly AF and it runs, but it takes about two to three minutes to completeSELECT p.project_id, p.hourly_rate, ex.sum_elapsed FROM projects p JOIN ( SELECT i.project_id, SUM(iv.seconds_elapsed) AS sum_elapsed FROM issues i LEFT JOIN intervals iv ON i.issue_id = iv.issue_id AND (i.internal_note NOT LIKE '%SKIF%') AND (iv.dt_start BETWEEN "2019-03-01T00:00:00Z" AND "2019-03-08T23:59:59Z") GROUP BY i.project_id ) AS ex ON p.project_id = ex.project_id AND p.project_name NOT LIKE 'K - %';
I haven't yet compared it to your work, but I'm almost certain that these are somewhat similar. I have 82 possible projects, 53.094 issues and 69.972 time intervals as of last friday. So some sort of handling has to be done in order for it to run in a timely fashion.
Don't add the condition filter on the LEFT join. This cause to match the records AND apply the filter at the same time. Put a standard WHERE instead which will prefilter the records and cut a ton load of iterations.
so the last line instead of
) AS ex ON p.project_id = ex.project_id AND p.project_name NOT LIKE 'K - %';
) AS ex ON p.project_id = ex.project_id WHERE p.project_name NOT LIKE 'K - %';