Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
FROM user_email_tracking ON u.id = email_tracking ?? That ON should not be there. –  ypercube Oct 7 '13 at 22:17
    
And show us the SELECT list as well. Do you really need the JOIN user_filter uf ? –  ypercube Oct 7 '13 at 22:20
    
@ypercube I need the count of things in uf for the user... that number is actually sent in an email to the end-user, so no avoiding it. –  djechlin Oct 7 '13 at 22:25
    
@ypercube no sorry, fixed... had it redundantly –  djechlin Oct 7 '13 at 22:25

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Your first version worked with finagling, and seems elegant enough to plausibly be the right thing to do. –  djechlin Oct 8 '13 at 20:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.