Why is MySQL is using so many temporary tables?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows

Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk


table_open_cache = 125 tables
table_definition_cache = 256 tables

You have a total of 97 tables

You have 125 open tables.

Current table_cache hit rate is 3%

Earlier temp table was of the 23725 temp tables 38% were created on disk but I changed max_heap and tmp_table to 200m from 16m and it lowered to 30%..

engine myisam 
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M

max_connections = 800

Another system with default configuration is showing of 23725 temp tables, 1% were created on disk

But i tried changing to default on the machine with this issue and it still shows Of 580 temp tables, 16% were created on disk

I am using Ubuntu 11.4 64 bit with 48 gb ram... Can any one suggest a solution?

Will changing the db engine from myisam to memory on tables using "group by" fix this?

Temporary tables are created and dropped as necessary, depending on the queries being executed. The figure you're seeing is the sum total of temporary tables created since the last time MySQL was started, not the number concurrently existing.

MySQL uses temporary tables when the query can't be calculated in a single pass. Switching the storage engine won't change that. The problem is with the query, not the configuration.

Increasing the tmp_table_size value will only prevent some of them from being written to disk, they will still be created in memory and filled up with data. This data probably comes from the disk in the first place although with 48GB of RAM you probably have quite a lot of it cached. Even cached, since 30% of these temporary tables are greater that 200MB, copying that amount of data around in RAM still takes time.

You can determine before even running a query whether it will use a temporary table or not by using the EXPLAIN syntax. Just put EXPLAIN before your query and it will output a bunch of information about the execution plan and the efficiency of the query without actually executing it.

You can probably find the queries that are causing these temporary tables because they will most likely be slow queries and hence will end up in your slow query log.

If you need help tuning specific queries, DBA.SE is a good place to go.

TL;DR

Tune your queries.