How to optimize a query that includes counts of rows in multiple tables? 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.

But - 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_filter against user_email_tracking on 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 user_filter (or user_email_tracking).

How to get SQL to do these things in the right order?

Engine is InnoDB.
My first instinct is use a big old join, count distinct on the ids of the secondary tables, and let the optimizer do it's thing.  Note, the tables you want to count should have a unique primary key.  I've assumed they are a single column with the name "id" for both tables in the query below.

SELECT ...
FROM USER u, COUNT(distinct et.id) as et_count, COUNT(distinct uf.id) as uf_count
LEFT OUTER JOIN user_email_tracking et 
    ON u.id = et.user_id
LEFT OUTER JOIN user_filter uf
    ON u.id = uf.user_id
WHERE u.stage = 10
    AND et.email_type_id = 4
GROUP BY u.id


That said, my other instinct is to put your subqueries into temporary tables with indexes. like...

CREATE TEMPORARY TABLE users (id INT PRIMARY KEY)

INSERT INTO users_temp (id)
SELECT id
FROM USER u
WHERE u.stage = 10


CREATE TEMPORARY TABLE email_tracking_count (user_id INT PRIMARY KEY, notified_count)

INSERT INTO email_tracking_count (user_id, notified_count)
SELECT u.id, COUNT(et.id)
FROM users_temp u
LEFT OUTER JOIN user_email_tracking et
        ON u.id = et.user_id
WHERE et.email_type_id = 4
GROUP BY u.id

CREATE TEMPORARY TABLE user_filter_count (user_id INT PRIMARY KEY, subscription_count)

INSERT INTO user_filter_count (user_id, subscription_count)
SELECT u.id, COUNT(uf.id)
FROM users_temp u
LEFT OUTER JOIN user_filter uf
        ON u.id = uf.user_id
GROUP BY u.id

SELECT ...
FROM users_temp ut
LEFT OUTER JOIN email_tracking_count etc
        ON ut.id = etc.user_id
LEFT OUTER JOIN user_filter_count ufc
        ON ut.id = ufc.user_id


That's a good bit longer, but I have a feeling it may perform better.  Try both out and let me know which works better.  I'll edit my answer accordingly.

disclaimer: I didn't run these queries, so theres probably a syntax error or typo somewher.