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