So I was working on the problem of optimizing the following query I have already optimized this to the fullest from my side can this be further optimized?
select distinct name ad_type from dim_ad_type x where exists ( select 1 from sum_adserver_dimensions sum left join dim_ad_tag_map on dim_ad_tag_map.id=sum.ad_tag_map_id and dim_ad_tag_map.client_id=sum.client_id left join dim_site on dim_site.id = dim_ad_tag_map.site_id left join dim_geo on dim_geo.id = sum.geo_id left join dim_region on dim_region.id=dim_geo.region_id left join dim_device_category on dim_device_category.id=sum.device_category_id left join dim_ad_unit on dim_ad_unit.id=dim_ad_tag_map.ad_unit_id left join dim_monetization_channel on dim_monetization_channel.id=dim_ad_tag_map.monetization_channel_id left join dim_os on dim_os.id = sum.os_id left join dim_ad_type on dim_ad_type.id = dim_ad_tag_map.ad_type_id left join dim_integration_type on dim_integration_type.id = dim_ad_tag_map.integration_type_id where sum.client_id = 50 and dim_ad_type.id=x.id ) order by 1
Advertisement
Answer
Your query although joined ok, is an overall bloat. You are using the dim_ad_type table on the outside, just to make sure it exists on the inside as well. You have all those left-joins that have NO bearing on the final outcome, why are they even there. I would simplify by reversing the logic. By tracing your INNER query for the same dim_ad_type table, I find the following is the direct line. sum -> dim_ad_tag_map -> dim_ad_type. Just run that.
select distinct dat.name Ad_Type from sum_adserver_dimensions sum join dim_ad_tag_map tm on sum.ad_tag_map_id = tm.id and sum.client_id = tm.client_id join dim_ad_type dat on tm.ad_type_id = dat.id where sum.client_id = 50 order by 1
Your query was running ALL dim_ad_types, then finding all the sums just to find those that matched. Run it direct starting with the one client, then direct with JOINs.