title: MySQL best practices for huge table
body: <p>I need help guys. I am no db expert and try to learn.</p>
  
  <p>1)</p>
  
  <p>I have a table with 45 columns and 100m records. (adding ~1 million every hour)
  I need to generate reports from this table. When the row counts were only a few hundreds, my query was sth like this and working pretty well:</p>
  
  <pre><code>SELECT 
      COUNT(CASE EVENT_TYPE WHEN 'imp' THEN 1 ELSE NULL END) as imps, 
      COUNT(CASE EVENT_TYPE WHEN 'click' THEN 1 ELSE NULL END) as clicks, 
      COUNT(CASE EVENT_TYPE WHEN 'pc_conv' THEN 1 ELSE NULL END) as post_click_convs, 
      COUNT(CASE EVENT_TYPE WHEN 'pv_conv' THEN 1 ELSE NULL END) as post_view_convs, 
      SUM(booked_revenue_dollars) as booked_revenue, 
      TRIM(site_domain) as site_domain, 
      campaign_id 
  
  FROM 
      lld_standards 
  
  WHERE 
      `datetime` &gt;= '2014-05-10 00:00:00' AND `datetime` &lt;= '2014-05-10 23:59:00'
  
  GROUP BY 
      campaign_id, 
      site_domain
  
  HAVING
      COUNT(CASE EVENT_TYPE WHEN 'imp' THEN 1 ELSE NULL END) &gt; 100;
  </code></pre>
  
  <p>I created index for <code>datetime</code>
  For most reports, only the <code>GROUP BY</code> part changes.</p>
  
  <p><strong>So how can I improve the query?</strong></p>
  
  <p>2)</p>
  
  <p>I am using RDS now (m3.large) and planning to switch to Redshift. <strong>Do you think it is the right move now?</strong></p>
  
  <p>Thank you.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>Here is the result of EXPLAIN:
  <img src="https://i.stack.imgur.com/elbPc.png" alt="EXPLAIN"></p>