Bug #26300 Description of Created_tmp_tables / Created_tmp_disk_tables status variables
Submitted: 12 Feb 2007 23:18 Modified: 15 Oct 2009 14:29
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: MC Brown CPU Architecture:Any
Tags: Created_tmp_disk_tables, Created_tmp_tables

[12 Feb 2007 23:18] Roland Bouman
Description:
In http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html, part of the description of Created_tmp_tables should be mentioned where Created_tmp_disk_tables is discussed. That description also contains a bit which should be mentioned where tmp_table_size is described

"
Created_tmp_disk_tables

The number of temporary tables on disk created automatically by the server while executing statements. 

...

Created_tmp_tables

The number of in-memory temporary tables created automatically by the server while executing statements. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based. 
"

How to repeat:
open http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html, find Created_tmp_tables

Suggested fix:
Please add relevant parts of the description of Created_tmp_tables to the description of tmp_table_size and Created_tmp_disk_tables.

(btw: I can't find description of tmp_table_size, I expected to find it in http://dev.mysql.com/doc/refman/5.1/en/set-option.html. Created_tmp_disk_tables is described as expected in http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html)
[12 Feb 2007 23:22] Mark Leith
If you are going to be making changes here I would also like to see some reference (*anywhere* would do) noting that temporary tables are created on disk straight away if the temporary tables would include TEXT/BLOB columns - in this case increasing tmp_table_size would have no affect if you did have a large number of Created_tmp_disk_tables.

I have never seen any reference to this anywhere within the manual.
[13 Feb 2007 8:16] Sveta Smirnova
Thank you for the report.

Verified as described.
[15 Oct 2009 14:29] MC Brown
I've made sure the advice on temporary table sizes and the stats is now consistent across the different variables. I've also add the following paragraph: 

          You can compare the number of disk tables created to the total
          number of tables created by comparing the values of the
          <literal role="statvar">Created_tmp_tables</literal> and
          <literal role="statvar">Created_tmp_disk_tables</literal>
          variables.

Which should highlight how to compare the values appropriately. 

On the issue of temporary tables being stored on disk when there is a BLOB or TEXT column, we have a reference to this in http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

However, I have made a minor clarification in this sentence: 

Some conditions prevent the use of a MEMORY temporary table, in which case the server uses a MyISAM table instead:

To read: 

Some conditions prevent the use of a MEMORY temporary table, in which case the server uses a MyISAM (on disk) table instead:

(addition of 'on disk') to highlight that the table will be created using a MyISAM *disk-based* table.
[7 Dec 2012 10:50] anh nx
I have a problem with created_tmp_tables and created_tmp_disk_tables:
myconfig:
tmp_table_size: 18M
max_heap_table_size:16M
I have a query:
----------------------------------------
SELECT A.Created_tmp_tables, B.Created_tmp_disk_tables
FROM
(SELECT G.`variable_value` as Created_tmp_tables
FROM `information_schema`.GLOBAL_Status G
 WHERE G.variable_name = "Created_tmp_tables" ) A,
(SELECT G.`variable_value` as Created_tmp_disk_tables
FROM `information_schema`.GLOBAL_Status G
WHERE G.variable_name = "Created_tmp_disk_tables" ) B;
----------------------------------
execute query, result is :
created_tmp_tables: 124
created_tmp_disk_tables:13
----------------------------
execute query again, result is:
created_tmp_tables: 128
created_tmp_disk_tables:13
----------------------------------------
Why does created_tmp_tables increases 128?
----------------------------------------
when I execute any query,why created_tem_tables always increase?
--------------------------------------
I have a query :
SELECT G.`variable_value` as Created_tmp_disk_tables
FROM `information_schema`.GLOBAL_Status G
WHERE G.variable_name = "Created_tmp_disk_tables" ;
----------
result:
created_tmp_disk_tables:14
when I execute query again, created_tmp_disk_tables increases become 1
--> Why created_tem_disk_tables increases?