title: MYSQL Query Optimization (need to increase speed)
body: <p>I have a MySQL Table that holds Google Analytics data:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `analytics_data` (
    `ga_profile_id` int(11) NOT NULL,
    `page` varchar(200) NOT NULL,
    `source` varchar(150) NOT NULL,
    `medium` varchar(50) NOT NULL,
    `keyword` varchar(200) NOT NULL,
    `bounces` int(11) NOT NULL,
    `entrances` int(11) NOT NULL,
    `exits` int(11) NOT NULL,
    `new_visits` int(11) NOT NULL,
    `page_views` int(11) NOT NULL,
    `unique_page_views` int(11) NOT NULL,
    `time_on_page` int(11) NOT NULL,
    `visits` int(11) NOT NULL,
    `date` date NOT NULL,
    KEY `ga_profile_id` (`ga_profile_id`,`source`,`medium`,`date`),
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>I have a query to compute the sum of visitors based on a google analytics profile ID (ga_profile_id) over a given time period:</p>
  
  <pre><code>SELECT 
      SUM( `visits` ), ( UNIX_TIMESTAMP( `date` ) - 21600 ) * 1000 AS date 
  FROM `analytics_data` 
  WHERE 
      `date` &gt;= '2011-05-09' AND `date` &lt;= '2011-06-08' AND `ga_profile_id` = [...]
  GROUP BY `date`
  </code></pre>
  
  <p>We have 4.5 million records or so.</p>
  
  <p><strong>Index Data:</strong></p>
  
  <pre><code>Type: BTREE
  Fields/Cardinality:
  ga_profile_id / 100
  source / 10196
  medium / 10196
  date / 149893
  </code></pre>
  
  <p><strong>EXPLAIN SELECT</strong><br>
  - <strong>id</strong>: 1<br>
  - <strong>select_type</strong>: SIMPLE<br>
  - <strong>table</strong>: analytics_data<br>
  - <strong>type</strong>: ref<br>
  - <strong>possible_keys</strong>: ga_profile_id<br>
  - <strong>key</strong>: ga_profile_id<br>
  - <strong>ref</strong>: const<br>
  - <strong>rows</strong>: 219555<br>
  - <strong>extra</strong>: Using where; Using temporary; Using filesort</p>
  
  <p>Average time for execution: <strong>1 second</strong>.</p>
  
  <p>We are on a virtual private server and most queries get executed in .0003 - 0.03 seconds. LONG queries (that I was going to optimize at some point) are generally .3 seconds.</p>
  
  <p>I have tried adjusting the keys, ignoring some, changing some values and nothing seems to be affecting it in a positive way. Considering this is 1 of many queries on a page.</p>
  
  <p>I am looking at changing MyISAM to memory -- any ideas are welcomed.</p>