[Solved] query taking too much time with irritated performance [closed]

Question

You don’t indicate any indexes. You will need them to improve performance.

OK, I don’t understand the part after the semi-colon, so I’ll ignore that.

Your whole query does a count(*) of a sub select, but you are doing a bunch of aggregation in the sub-query (calling SUM) which is work the database doesn’t have to do, so eliminate that.

You do aggregation in further sub-queries, so cut that out.

In your main sub-select, where you are just doing a count(*), you select fields that are not in your group by, which is work on the database that is not affecting the count, so get rid of that.

That gives us something like…

select count(*)
from (select paid.keyword_id
      from `t_keyword_paid_analytics_google_ib` paid
        left join
          (select outer_t.keyword_id
           from t_keyword_conversion_ga_ib outer_t
           where ...
           group by outer_t.keyword_id  ) con
          on paid.keyword_id = con.keyword_id
        left join
          (select outer_t.keyword_id
           from t_keyword_stat_ga_ib outer_t
           where ...
           group by outer_t.keyword_id) stat on paid.keyword_id = stat.keyword_id
        left join `t_managed_keyword_ib` keyword on keyword.id = paid.keyword_id
     where ...
     group by paid.keyword_id
     having paid.keyword_id is not null
    ) tempt;

Also, counts may be faster than selects and you seem to be looking for the keyword_id and counting them in the end. If you can count as you go that would be better than selecting. I’m not sure if you can apply that to this query, but keep it in mind.

Accepted Answer