title: Mysql query optimization for union all query
body: <p>I am new to mysql and keen to know what configuration do i need to have to support 50 millions of read  data from mysql 5.7
  Mysql server configuration is 125 gb memory and 100 GB disk space.</p>
  <p>I want to know what global and threads configuration do i need to set like innodb_buffer size etc to handle high data efficiently.</p>
  <p>I am feeding the data into data-tables using php/mysql 5.7 and it is really hectic and taking around 5 mins.</p>
  <p>Below is the query,</p>
  <pre><code>SELECT
  `u`.`user_id`,
  IF(
      MAX(ac.account_type) = 'ADVERTISER',
      MAX(a.clientname),
      MAX(p.name)
  ) AS contact_name,
  `ac`.`account_type`,
  MAX(c.campaignid) AS campaignid,
  MAX(c.campaignname) AS campaignname,
  MAX(z.zoneid) AS zoneid,
  MAX(z.zonename) AS zonename,
  MAX(c.clientid) AS clientid,
  MAX(s.date_time) AS date_time,
  SUM(s.requests) AS total_requests,
  SUM(s.impressions) AS total_views,
  SUM(s.clicks) AS total_clicks,
  SUM(s.conversions) AS total_conversions,
  SUM(s.total_revenue) AS total_revenue,
  SUM(s.total_basket_value) AS total_basket_value,
  SUM(s.total_num_items) AS total_num_items,
  MAX(z.delivery) AS delivery
  FROM
      (
      SELECT
          interval_start AS date_time,
          creative_id AS ad_id,
          zone_id,
          0 AS impressions,
          0 AS clicks,
          0 AS conversions,
          0 AS requests,
          total_cost * COUNT AS total_revenue,
          0 AS total_basket_value,
          0 AS total_num_items
      FROM
          rv_data_bkt_revenue
      WHERE
          creative_id &gt; 0
      UNION ALL
  SELECT
      interval_start AS date_time,
      creative_id AS ad_id,
      zone_id,
      0 AS impressions,
      COUNT AS clicks,
      0 AS conversions,
      0 AS requests,
      0 AS total_revenue,
      0 AS total_basket_value,
      0 AS total_num_items
  FROM
      rv_data_bkt_c
  WHERE
      creative_id &gt; 0
  UNION ALL
  SELECT
      interval_start AS date_time,
      creative_id AS ad_id,
      zone_id,
      COUNT AS impressions,
      0 AS clicks,
      0 AS conversions,
      0 AS requests,
      0 AS total_revenue,
      0 AS total_basket_value,
      0 AS total_num_items
  FROM
      rv_data_bkt_m
  WHERE
      creative_id &gt; 0
  UNION ALL
  SELECT
      MAX(date_time) AS date_time,
      creative_id AS ad_id,
      MAX(zone_id) AS zone_id,
      0 AS impressions,
      0 AS clicks,
      COUNT(creative_id) AS conversions,
      0 AS requests,
      0 AS total_revenue,
      0 AS total_basket_value,
      0 AS total_num_items
  FROM
      rv_data_bkt_a
  WHERE
      creative_id &gt; 0
  GROUP BY
      creative_id
  UNION ALL
  SELECT
      *
  FROM
      (
      SELECT
          interval_start AS date_time,
          ad_id,
          zone_id,
          impressions,
          clicks,
          conversions,
          requests,
          0 AS total_revenue,
          total_basket_value,
          total_num_items
      FROM
          rv_data_intermediate_ad
      WHERE
          ad_id &gt; 0
  ) e
  ) AS s
  INNER JOIN rv_zones AS z
  ON
      `z`.`zoneid` = `s`.`zone_id`
  INNER JOIN rv_banners AS b
  ON
      `s`.`ad_id` = `b`.`bannerid`
  INNER JOIN rv_campaigns AS c
  ON
      `b`.`campaignid` = `c`.`campaignid`
  LEFT JOIN rv_affiliates AS p
  ON
      `z`.`affiliateid` = `p`.`affiliateid`
  LEFT JOIN rv_clients AS a
  ON
      `a`.`clientid` = `c`.`clientid`
  INNER JOIN rv_users AS u
  ON
      `u`.`default_account_id` = `a`.`account_id` OR `u`.`default_account_id` = `p`.`account_id`
  INNER JOIN rv_accounts AS ac
  ON
      `u`.`default_account_id` = `ac`.`account_id` OR `u`.`default_account_id` = `ac`.`account_id` AND `u`.`default_account_id` IS NOT NULL
  WHERE
      `ac`.`account_id` IN(
          '7',
          '9',
          '10'
      )
  GROUP BY
      `u`.`user_id`,
      `ac`.`account_type`
  </code></pre>