Problem query

query.sql

Here's my query:


    SELECT SQL_BUFFER_RESULT SQL_BIG_RESULT users.id, users.email, 
            COUNT(av.user_id) AS article_views_count,
            COUNT(af.id) AS article_favorites_count,
            COUNT(lc.user_id) AS link_clicks_count,
            COUNT(ai.user_id) AS ad_impressions_count,
            COUNT(ac.user_id) AS ad_clicks_count
              FROM users
                LEFT JOIN article_views AS av     ON (av.user_id = users.id AND av.created_at >= '2012-11-28 00:00:00' AND av.created_at <= '2012-11-30 23:59:59')
                LEFT JOIN article_favorites AS af ON (af.user_id = users.id AND af.created_at >= '2012-11-28 00:00:00' AND af.created_at <= '2012-11-30 23:59:59')
                LEFT JOIN link_clicks AS lc       ON (lc.user_id = users.id AND lc.created_at >= '2012-11-28 00:00:00' AND lc.created_at <= '2012-11-30 23:59:59')
                LEFT JOIN ad_impressions AS ai    ON (ai.user_id = users.id AND ai.created_at >= '2012-11-28 00:00:00' AND ai.created_at <= '2012-11-30 23:59:59')
                LEFT JOIN ad_clicks AS ac         ON (ac.user_id = users.id AND ac.created_at >= '2012-11-28 00:00:00' AND ac.created_at <= '2012-11-30 23:59:59')
              GROUP BY users.id
              HAVING (article_views_count + article_favorites_count + link_clicks_count + ad_impressions_count + ad_clicks_count) > 0

Some stats to give you context:

 1. users: 1,474,348
 2. article_views: 32,603,637
 3. article_favorites: 10,199
 4. link_clicks: 4,258,901
 5. ad_impressions: 66,758,573
 6. ad_clicks: 324,125

Every table that is joined in has a composite index on user_id and created_at (in that order).

The goal is to only return users that have any activity (view, favorite, click, impression, ad click) within the time period.


Any ideas to optimize this bad boy?