MySQL Forums
Forum List  »  Optimizer & Parser

Complex query - need advise
Posted by: chris boer
Date: November 03, 2012 04:16PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Complex query - need advise
2879
November 03, 2012 04:16PM
1410
November 03, 2012 04:24PM
1391
November 04, 2012 10:05AM
1714
November 05, 2012 11:12AM


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.