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
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
