gistfile1.txt

SELECT
COUNT(*) AS click_count,
SUM(c.total_cost_to_advertiser) AS total_cost_to_advertiser,
SUM(c.optimizer_bid_price) AS optimizer_bid_price,
SUM(c.optimizer_pending_earnings) AS optimizer_pending_earnings,
SUM(c.optimizer_paid_amount) AS optimizer_paid_amount,
SUM(c.market_rake_amount) AS market_rake_amount,
SUM(c.advertiser_refund) AS advertiser_refund,
SUM(c.ad_network_cost) AS ad_network_cost,
SUM(c.ad_network_refund) AS ad_network_refund,
c.campaign_group_id,c.optimizer_id,c.ad_network_id
FROM
click_registers c USE INDEX (fk_click_registers_dw_date_markers,fk_cr_banked_date_markers,fk_cr_paid_date_markers)
INNER JOIN mirror_daily_ad_network_optimizer_campaign_groups ON 
( c.campaign_group_id = mirror_daily_ad_network_optimizer_campaign_groups.campaign_group_id  
	AND  c.optimizer_id = mirror_daily_ad_network_optimizer_campaign_groups.optimizer_id  
	AND  c.ad_network_id = mirror_daily_ad_network_optimizer_campaign_groups.ad_network_id  
	AND c.dw_date_marker_id  <= 1100 
	AND banked_date_marker_id <= 1100 
	AND (paid_date_marker_id IS NULL OR paid_date_marker_id > 1100)
)
GROUP BY c.campaign_group_id,c.optimizer_id,c.ad_network_id
ORDER BY NULL;

602 rows in set (2.85 sec)





but should the AND c.dw_date_marker_id  <= 1100 ,AND banked_date_marker_id <= 1100, AND (paid_date_marker_id IS NULL OR paid_date_marker_id > 1100)
) be moved out of the JOIN?


SELECT
COUNT(*) AS click_count,
SUM(c.total_cost_to_advertiser) AS total_cost_to_advertiser,
SUM(c.optimizer_bid_price) AS optimizer_bid_price,
SUM(c.optimizer_pending_earnings) AS optimizer_pending_earnings,
SUM(c.optimizer_paid_amount) AS optimizer_paid_amount,
SUM(c.market_rake_amount) AS market_rake_amount,
SUM(c.advertiser_refund) AS advertiser_refund,
SUM(c.ad_network_cost) AS ad_network_cost,
SUM(c.ad_network_refund) AS ad_network_refund,
c.campaign_group_id,c.optimizer_id,c.ad_network_id
FROM click_registers c USE INDEX (fk_click_registers_dw_date_markers,fk_cr_banked_date_markers,fk_cr_paid_date_markers)
INNER JOIN mirror_daily_ad_network_optimizer_campaign_groups ON 
( c.campaign_group_id = mirror_daily_ad_network_optimizer_campaign_groups.campaign_group_id  
	AND  c.optimizer_id = mirror_daily_ad_network_optimizer_campaign_groups.optimizer_id  
	AND  c.ad_network_id = mirror_daily_ad_network_optimizer_campaign_groups.ad_network_id  
	)
WHERE c.dw_date_marker_id  <= 1100
AND c.banked_date_marker_id <= 1100 
AND (c.paid_date_marker_id IS NULL OR c.paid_date_marker_id > 1100)
GROUP BY c.campaign_group_id,c.optimizer_id,c.ad_network_id
ORDER BY NULL;

602 rows in set (2.85 sec)