Optimal MySQL temporary tables (memory tables) configuration?

Question

Hello, excuse my english

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 GROUP BY that i can´t avoid and that is the cause of creating temporary tables. My configuration was:

max_heap_table_size = 16M
tmp_table_size = 32M

The result:
temp table to disk percent + - 12.5%

Then I changed my settings, according to this post, http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/

max_heap_table_size = 128M
tmp_table_size = 128M

The result:
temp table to disk percent + - 18%

The results were not expected, do not understand why.

It is wrong tmp_table_size = max_heap_table_size? Should not increase the size?

Query

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;

EXPLAIN

| 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 |

Thank you very much

Answer 1

Maybe try posting your query along with EXPLAIN, we'll see if that GROUP BY cannot be optimized.