I have moved this question. Thank you for all the answers.

CodePudding user response：

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.