title: Optimal MySQL temporary tables (memory tables) configuration?
body: <p>First of all, I am new to optimizing mysql. The fact is that I have in my web application (around 400 queries per second), a query that uses a <code>GROUP BY</code> that i can´t avoid and that is the cause of creating temporary tables. My configuration was:</p>
  
  <pre><code>max_heap_table_size = 16M  
  tmp_table_size = 32M  
  </code></pre>
  
  <p>The result: temp table to disk percent + - 12.5%</p>
  
  <p>Then I changed my settings, according to <a href="http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/" rel="noreferrer">this post</a></p>
  
  <pre><code>max_heap_table_size = 128M  
  tmp_table_size = 128M
  </code></pre>
  
  <p>The result: temp table to disk percent + - 18%</p>
  
  <p>The results were not expected, do not understand why.</p>
  
  <p>It is wrong <code>tmp_table_size = max_heap_table_size</code>?
  Should not increase the size?</p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>SELECT images, id  
  FROM classifieds_ads   
  WHERE  parent_category = '1' AND published='1' AND outdated='0'
  GROUP BY aux_order  
  ORDER BY date_lastmodified DESC  
  LIMIT 0, 100;
  </code></pre>
  
  <p><strong>EXPLAIN</strong></p>
  
  <pre><code>| 1 |SIMPLE|classifieds_ads | ref |parent_category, published, combined_parent_oudated_published, oudated | combined_parent_oudated_published | 7 | const,const,const | 67552 | Using where; Using temporary; Using filesort |
  </code></pre>