title: query taking too much time with irritated performance
body: <ol>
  <li>My query is taking very long time around 1hr</li>
  <li>it was impacting on DB performance.</li>
  <li>even for Explain plan also it was taking very long time.</li>
  <li>please rewrite the query for optimal performance.</li>
  </ol>
  
  <p>Query:</p>
  
  <pre><code>SELECT Count(*)
  FROM   (SELECT paid.keyword_id,
                 paid.keyword_name,
                 stat.orgentrances,
                 keyword.rank1,
                 keyword.rank_check,
                 Sum(paid.clicks)                                     AS sumclick,
                 Sum(paid.clicks * paid.avg_position)                 AS
                 sumclickavgpos,
                 Sum(paid.itemrevenue)                                AS sumitem,
                 Sum(paid.cost)                                       AS sumcost,
                 Sum(paid.transactions)                               AS sumtrans,
                 Sum(paid.impressions)                                AS
                 sumimpress,
                 IF(Sum(paid.impressions) = 0, 0, Sum(
                 paid.impressions * paid.avg_position) / Sum
                                                  (paid.impressions)) AS
                        sumimpressavgrank,
                 con.item_revenue,
                 con.transactions,
                 keyword.monthly_search_volume
          FROM   `t_keyword_paid_analytics_google_ib` paid
                 LEFT JOIN (SELECT outer_t.keyword_id,
                                   Sum(outer_t.item_revenue) AS item_revenue,
                                   Sum(outer_t.transactions) AS transactions
                            FROM   t_keyword_conversion_ga_ib outer_t
                            WHERE  outer_t.own_domain_id = 720
                                   AND outer_t.traffic_date &gt;= '2012-12-01'
                                   AND outer_t.traffic_date &lt;= '2012-12-31'
                            GROUP  BY outer_t.keyword_id) con
                        ON paid.keyword_id = con.keyword_id
                 LEFT JOIN (SELECT outer_t.keyword_id,
                                   Sum(outer_t.entrances) AS orgEntrances
                            FROM   t_keyword_stat_ga_ib outer_t
                            WHERE  outer_t.own_domain_id = 720
                                   AND outer_t.traffic_date &gt;= '2012-12-01'
                                   AND outer_t.traffic_date &lt;= '2012-12-31'
                                   AND ( outer_t.medium = 'organic'
                                          OR outer_t.medium IS NULL )
                            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  paid.own_domain_id = 720
                 AND paid.traffic_date &gt;= '2012-12-01'
                 AND paid.traffic_date &lt;= '2012-12-31'
                 AND ( paid.channel IS NULL
                        OR paid.channel = 'Google' )
          GROUP  BY paid.keyword_id
      HAVING paid.keyword_id IS NOT NULL) tempt; 
  
  (outer_t.medium = 'organic' or outer_t.medium is null ) after outer_t.own_domain_id = 720 and (paid.channel is null or paid.channel = 'Google') 
  after paid.own_domain_id = 720
  </code></pre>
  
  <p>Tables Structure:</p>
  
  <pre><code>mysql&gt; show create table t_keyword_paid_analytics_google_ib\G
  *************************** 1. row ***************************
  Table: t_keyword_paid_analytics_google_ib
  Create Table: CREATE TABLE `t_keyword_paid_analytics_google_ib` (
  `keyword_name` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL,
  `target_url_id` int(11) DEFAULT NULL,
  `own_domain_id` int(11) NOT NULL,
  `log_date` date NOT NULL,
  `traffic_date` date NOT NULL,
  `impressions` int(11) DEFAULT NULL,
  `clicks` int(11) DEFAULT NULL,
  `entrances` int(11) DEFAULT NULL,
  `match_type` int(11) DEFAULT NULL COMMENT '1: Phrase, 2: Exact, 3:Broad 4: etc',
  `ad_group_name` varchar(200) DEFAULT NULL,
  `ad_distribution_network` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  `match_query` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  `cost` decimal(10,2) DEFAULT NULL,
  `cpm` decimal(10,2) DEFAULT NULL,
  `ctr` decimal(10,2) DEFAULT NULL COMMENT 'percent',
  `cpc` decimal(10,2) DEFAULT NULL,
  `campaign` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
  `keyword_status` tinyint(4) DEFAULT NULL COMMENT '1: Active,2: Approved, 3: Disapproved, 4: Paused , 5:Pending, 6: Failed, 7:etc',
  `ad_group_status` tinyint(4) DEFAULT NULL COMMENT '1:ELIGIBLE; 2=PAUSED;3=LOW_SEARCH_VOLUME;4 =LOW_QUALITY_SCORE; 5=DISAPPROVED; 6=AD_GROUP_PAUSED; 7=etc',
  `max_cpc` decimal(10,2) DEFAULT NULL,
  `quality_score` tinyint(4) DEFAULT NULL,
  `channel` varchar(100) DEFAULT NULL,
  `first_page_cpc` decimal(10,2) DEFAULT NULL,
  `avg_position` decimal(10,2) DEFAULT NULL,
  `itemRevenue` decimal(10,2) DEFAULT NULL,
  `goal1value` decimal(10,2) DEFAULT NULL,
  `goal2value` decimal(10,2) DEFAULT NULL,
  `goal3value` decimal(10,2) DEFAULT NULL,
  `goal4value` decimal(10,2) DEFAULT NULL,
  `transactions` int(10) DEFAULT NULL,
  `goal1completions` int(10) DEFAULT NULL,
  `goal2completions` int(10) DEFAULT NULL,
  `goal3completions` int(10) DEFAULT NULL,
  `goal4completions` int(10) DEFAULT NULL
  ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)
  
  mysql&gt; show create table t_keyword_conversion_ga_ib\G
  *************************** 1. row ***************************
  Table: t_keyword_conversion_ga_ib
  Create Table: CREATE TABLE `t_keyword_conversion_ga_ib` (
  `keyword_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `id` int(11) NOT NULL,
  `own_domain_id` int(11) DEFAULT NULL,
  `keyword_id` int(11) DEFAULT NULL,
  `traffic_date` date DEFAULT NULL,
  `targeturl_id` int(11) DEFAULT NULL,
  `entrance` int(11) DEFAULT NULL,
  `transactions` int(11) DEFAULT NULL,
  `item_revenue` decimal(9,2) DEFAULT NULL,
  `goal1completions` int(11) DEFAULT NULL,
  `goal2completions` int(11) DEFAULT NULL,
  `goal3completions` int(11) DEFAULT NULL,
  `goal4completions` int(11) DEFAULT NULL,
  `goal5completions` int(11) DEFAULT NULL,
  `goal6completions` int(11) DEFAULT NULL,
  `goal7completions` int(11) DEFAULT NULL,
  `goal8completions` int(11) DEFAULT NULL,
  `goal9completions` int(11) DEFAULT NULL,
  `goal10completions` int(11) DEFAULT NULL,
  `goal1Value` decimal(9,2) DEFAULT NULL,
  `goal2Value` decimal(9,2) DEFAULT NULL,
  `goal3Value` decimal(9,2) DEFAULT NULL,
  `goal4Value` decimal(9,2) DEFAULT NULL,
  `goal5Value` decimal(9,2) DEFAULT NULL,
  `goal6Value` decimal(9,2) DEFAULT NULL,
  `goal7Value` decimal(9,2) DEFAULT NULL,
  `goal8Value` decimal(9,2) DEFAULT NULL,
  `goal9Value` decimal(9,2) DEFAULT NULL,
  `goal10Value` decimal(9,2) DEFAULT NULL,
  `medium` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `source` varchar(255) COLLATE latin1_bin DEFAULT NULL
  ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>please help me here.</p>