MySQL Forums
Forum List  »  Optimizer & Parser

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

SELECT as email_job, ej.memo as memo,
        ej.create_date as `date`,
        concat(x.post_name, ' (',x.post_id,')') as post_name,
        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, as job_report_id,
           as job_id, as post_name,
                    count( 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
            join  email_job_message_report ejmr2
            join  post_to_campaign ptc2
                  on ptc2.campaign_id=ejmr2.campaign_id
            join  post p2 on
            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, ejmr2.campaign_id
            order by
        ) as x
    join  post p  on = x.post_id
    JOIN  post_to_campaign ptc  ON = 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 =
               AND  tr.type IN ('3', '4', '13', '2', '1')
               AND (   tr.status IS NULL
                   OR  tr.status NOT IN ('removed', 'duplicate', 'reversed'))
    WHERE = x.job_id
    order by;

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

Written By
April 20, 2010 10:02AM
April 22, 2010 09:53AM
Re: Help with Explain Plan
April 24, 2010 02:36PM
April 27, 2010 12:44PM
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.