List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 16 2001 3:03am
Subject:Re: What does tmp_table_size *really* do?
View as plain text  
In the last episode (Jun 15), Jeremy Zawodny said:
> The manual (http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html) states:
> 
>   If an in-memory temporary table exceeds this size, MySQL will
>   automatically convert it to an on-disk MyISAM table. Increase the
>   value of tmp_table_size if you do many advanced GROUP BY queries and
>   you have lots of memory.
> 
> I don't do many advanced GROUP BY queries. However, our server
> currently says:
> 
>   | Created_tmp_disk_tables  | 563408     |
>   | Created_tmp_tables       | 652927     |
> 
> After about a day of uptime. This bothers me. My tmp_table_size is set
> to 24MB. I'm about to increase it to 64MB and see what the results
> look like. (The machine has 1GB of RAM.) But can anyone describe in
> more detail what the conditions are under which MySQL will create a
> tmp_table? It has to be more than in GROUP BY operations.
> 
> What about ORDER BY? We do A LOT of ORDER BY...

30 seconds of cscope browsing on the mysql source shows the follwing
comments right before calls to create_tmp_table():

/* Create a tmp table if distinct or if the sort is too complicated */
/* group data to new table */

So I'd say Mysql will use temp tables on GROUP BY, DISTINCT, and
probably ORDER BY queries that mysql cannot satisfy with the same index
the optimizer picked.

The need_tmp flag is passed to the describe function, so any query that
requires a temp table should also have "Using temporary" in its explain
plan.

-- 
	Dan Nelson
	dnelson@stripped
Thread
What does tmp_table_size *really* do?Jeremy Zawodny16 Jun
  • Re: What does tmp_table_size *really* do?Dan Nelson16 Jun
    • Re: What does tmp_table_size *really* do?Jeremy Zawodny16 Jun
      • Re: What does tmp_table_size *really* do?Dan Nelson16 Jun
        • Re: What does tmp_table_size *really* do?Jeremy Zawodny16 Jun
          • Re: What does tmp_table_size *really* do?Dan Nelson17 Jun
            • Re: What does tmp_table_size *really* do?Jeremy Zawodny17 Jun
    • Re: What does tmp_table_size *really* do?jaab16 Jun
      • Re: What does tmp_table_size *really* do?Kevin Whitney16 Jun
      • RE: What does tmp_table_size *really* do?Sander Pilon16 Jun