My query looks like this:
EXPLAIN SELECT ... FROM USER u LEFT OUTER JOIN (SELECT COUNT(*) notified_count, user_id FROM user_email_tracking ON u.id = email_tracking WHERE email_type_id = 4 GROUP BY user_id) email_tracking ON u.id = email_tracking.user_id JOIN ( SELECT user_id, count(*) as subscriptionCount FROM user_filter GROUP BY user_id) uf on u.id = uf.user_id WHERE u.stage = 10
My intuition is this is pretty doable. That
u.stage = 10 narrows it down a lot. There's only a few hundred users who it applies to. If I could narrow down by that first then perform all other work, seems like this query should take a few seconds.
user_filter is huge, and
user_email_tracking is pretty big. (Both are indexed on their
user_id columns). If those have to get performed on everything, it's much slower. And I also must avoid joining
user_id, since that would be a huge times a huge table.
If I only had one count to do, I could move the
group by all the way outside and just have a row for each row of
How to get SQL to do these things in the right order?
Engine is InnoDB.