Bug #67839 created_tmp_tables and created_tmp_disk_tables
Submitted: 7 Dec 2012 15:41 Modified: 21 Dec 2012 8:55
Reporter: anh nx Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.1.38 OS:Any
Assigned to: CPU Architecture:Any

[7 Dec 2012 15:41] anh nx
Description:
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?

How to repeat:
comment on bug
[7 Dec 2012 16:15] Peter Laursen
.. because it is a [SESSION] status variable. 

The value is not for a single query but for current session/connection. Refer:
http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Created_tmp_ta...
.. so this particular query seems to create 3 temporary tables (in memory) every time it executes.

Peter
(not a MySQL/Oracle person)
[7 Dec 2012 16:31] Hartmut Holzgraefe
Materialization of INFORMATION_SCHEMA tables requires a temp table, both your subqueries touch I_S, so that accounts for an increase by 2. And then materialization of the subquery results: yet two more, totals 4

To verify: 

1) materialization of I_S tables on every use: run

     SELECT variable_value 
       FROM information_schema.GLOBAL_Status 
      WHERE variable_name = 'Created_tmp_tables';

   a few times, see how the counter goes up by one each time

2) materialization of subquery in FROM clause:

     show status like 'Created_tmp_tables'; 
     select a from (select 1 as a) tab; 
     show status like 'Created_tmp_tables';

   see how the counter goes up by one again
[10 Dec 2012 1:53] anh nx
Peter Laursen and Hartmut Holzgraefe
thanks for your answer
more a question:
How do know when MySQL Server creates tmp tables?
[21 Dec 2012 8:55] MySQL Verification Team
Please reference the manual page for more on this:

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html