MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help with Explain Plan
Posted by: Rick James
Date: April 24, 2010 02:36PM

SELECT  ej.id as email_job, ej.memo as memo,
        ej.create_date as `date`,
        concat(x.post_name, ' (',x.post_id,')') as post_name,
        x.post_id,
        x.volume,
        COUNT(IF(tr.type="1", 1, null)) AS opens,
        COUNT(IF(tr.type="2", 1, null)) AS clicks,
        sum(if(tr.type="2", tr.payout, 0)) AS total,
        x.complaints as complaints,
        x.blocked as blocked
    FROM  email_job ej, 
          ( SELECT  ej2.create_date as job_date, ejr2.id as job_report_id,
                    ej2.id as job_id, p2.name as post_name,
                    count(ejmr2.id) as volume, ptc2.post_id as post_id,
                    count(if(ejmr2.status = 'complaint', 1, NULL)) as complaints,
                    count(if(ejmr2.status = 'blocked', 1, NULL)) as blocked
            from  email_job ej2
            join  email_job_report ejr2 on ej2.id=ejr2.email_job_id
            join  email_job_message_report ejmr2
                  on ejr2.id=ejmr2.email_job_report_id
            join  post_to_campaign ptc2
                  on ptc2.campaign_id=ejmr2.campaign_id
            join  post p2 on p2.id=ptc2.post_id
            where  ej2.type='email'
              and  ej2.start_date >= '2010-04-15 00:00:00'
              and  ej2.start_date <= '2010-04-15 23:59:59'
            group by  ej2.id, ejmr2.campaign_id
            order by  ej2.id
        ) as x
    join  post p  on p.id = x.post_id
    JOIN  post_to_campaign ptc  ON p.id = ptc.post_id
    JOIN  email_job_message_report ejmr
           ON ejmr.email_job_report_id = x.job_report_id
              and  ejmr.campaign_id=ptc.campaign_id
    LEFT JOIN  tracking_request tr
           ON ejmr.to_profile_id = tr.profile_id
               AND (tr.post_id = p.id)
               AND  tr.type IN ('3', '4', '13', '2', '1')
               AND (   tr.status IS NULL
                   OR  tr.status NOT IN ('removed', 'duplicate', 'reversed'))
    WHERE  ej.id = x.job_id
    GROUP BY  ej.id, p.id
    order by  ej.id;

I need most of this info to further analyze the situation:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Also, how long does the subquery take by itself?

COUNT(IF(tr.type="2", 1, null)) AS clicks,
--> (slightly shorter)
SUM(tr.type='2') AS clicks

Options: ReplyQuote


Subject
Views
Written By
Posted
2774
April 20, 2010 10:02AM
1739
April 22, 2010 09:53AM
Re: Help with Explain Plan
1597
April 24, 2010 02:36PM
1695
April 27, 2010 12:44PM
1341
April 29, 2010 09:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.