Complex query - need advise

[chris boer]

Hi guys,

I need some advice, aggregates across multiple joins, query is running horribly slow on a small amazon mysql RDS DB. Any ideas?

SELECT 'Name publisher' AS Publisher,
t.`Campaign Name` AS CampaignName,
c.Impressions AS ProviderImpressions,
c.Clicks AS ProviderClicks,
t.`Date` AS SearchDate,
t.Impressions AS TotalServedImpressions,
t.Clicks AS TotalClicks,
ROUND(t.GrossMediaCost, 2) AS GrossMediaCost,
ROUND(((t.Clicks / t.Impressions) * 100), 3) AS CTR,
ROUND((((c.Impressions - t.Impressions) / t.Impressions) * 100), 2) AS PercImp,
ROUND((((c.Clicks - t.Clicks) / t.Clicks) * 100), 2) AS CalcClicks,
ROUND((t.GrossMediaCost / (c.Impressions / 1000)), 2) AS ECPM,
ROUND((t.GrossMediaCost / c.Clicks), 5) AS ECPC
FROM (SELECT sum(Impressions) AS Impressions, sum(Clicks) AS Clicks
FROM Delivery
WHERE AdID IN
(SELECT distinct d.AdID
FROM MetaData m, Delivery d, Media mm
WHERE m.AdID = d.AdID and m.PlacementID = mm.`Placement ID 3PAS`
AND m.CampaignName = 'campaign name'
AND date_format(str_to_date(mm.`Date`, '%d-%m-%Y'), '%Y-%m-%d') = '2012-10-29')
) c,
(SELECT `Campaign Name`, `Date`, sum(Impressions) AS Impressions, sum(Clicks) AS Clicks, sum(`Gross Media Cost`) AS GrossMediaCost
FROM Media
WHERE`Placement ID 3PAS` IN
(SELECT distinct mm.`Placement ID 3PAS`
FROM MetaData m, Media mm
WHERE m.PlacementID = mm.`Placement ID 3PAS`
AND m.CampaignName = 'campaign name'
AND date_format(str_to_date(mm.`Date`, '%d-%m-%Y'), '%Y-%m-%d') = '2012-10-29')
) t
GROUP BY CampaignName, SearchDate
ORDER BY CampaignName, SearchDate