title: How to avoid temporary table in sql query?
body: <p>I am running a query (taking 3 minutes to execute)-</p>
  <pre><code>SELECT c.eveDate,c.hour,SUM(c.dataVolumeDownLink)+SUM(c.dataVolumeUpLink) 
  FROM cdr c 
  WHERE c.evedate&gt;='2013-10-19'
  GROUP BY c.hour;
  </code></pre>
  <p>with explain plan -</p>
  <pre><code>id  select_type  table  type  possible_keys                 key  key_len  ref  rows      Extra                                         
  1   SIMPLE       c      ALL   evedate_index,eve_hour_index                     31200000  Using where; Using temporary; Using filesort  
  </code></pre>
  <p>I am using table(Myisam)-</p>
  <blockquote>
  <p>Primary key(id,evedate),</p>
  <p>with weekly 8 partitions with key evedate,</p>
  <p>index key- on evedate,</p>
  <p>composite index on evedate,hour.</p>
  </blockquote>
  <p>I have changed the mysql tuning parameters from my.ini as (4GB RAM)-</p>
  <blockquote>
  <p>tmp_table_size=200M</p>
  <p>key_buffer_size=400M</p>
  <p>read_rnd_buffer_size=2M</p>
  </blockquote>
  <p>But still its using temporary table and file sort. Please let me know what should I do to exclude this.</p>
  <p><em>After adding new composite index(evedate,msisdn)</em></p>
  <p>I have found some changes in few queries they were not using any temporary case, even in above query if I omit group by clause, its not using temporary table.</p>