MySQL Bugs: #49175: Small clarification on Created_tmp_disk_tables and Created_tmp

Bug #49175	Small clarification on Created_tmp_disk_tables and Created_tmp_tables in manual
Submitted:	28 Nov 2009 5:19	Modified:	1 Dec 2009 19:03
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	Any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
At http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html the manual lists:

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.'

However, this may be somewhat confusing, considering TEMPORARY tables (those created with CREATE TEMPORARY).

How to repeat:
Values not raised for creation of a TEMPORARY table:

---
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> CREATE TEMPORARY TABLE TESTDISK (ID INT) ENGINE=MYISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW STATUS LIKE '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)
---

But are when an internal temporary table is needed:
---
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE TESTDISK (ID INT) ENGINE=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TESTDISK VALUES (1),(2),(3),(4),(5),(8),(6),(7);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO TESTDISK SELECT A.ID FROM TESTDISK AS A, TESTDISK AS B, TESTDISK AS C, TESTDISK AS D;
Query OK, 4096 rows affected (0.02 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> SHOW STATUS LIKE '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)
---

Suggested fix:
Created_tmp_disk_tables 
'The number of internal temporary tables on disk created automatically by the server while executing statements. This does not include TEMPORARY tables like those created with CREATE TEMPORARY TABLE.'

Created_tmp_tables 
'The number of internal and in-memory temporary tables created automatically by the server while executing statements. This does not include TEMPORARY tables like those created with CREATE TEMPORARY TABLE.'

I think "created automatically by the server" properly explains what kind of tables authors mean. CREATE TEMPORARY TABLE ... statement is explicitly sent by the user or application, not automatically, implicitly executed by server. I'd say "Not a bug".

Highlight added:

Created_*tmp*_disk_tables 
'The number of *tem*porary tables on disk created automatically by the server while executing statements.'

Conveys:

- 'There were a number of temporary tables, created on disk'
- 'These were created automatically, by the server'
- 'They were created while executing statements'

It does not convey:

- If these tables are *internal* *tem*porary tables, or *TEM*PORARY tables like those created by CREATE TEMPORARY TABLE ... (see bug #49176 as well)

  Note that I added the word 'internal', besides the 'CREATE TEMPORARY TABLE' clarification.

----

> CREATE TEMPORARY TABLE ... statement is explicitly sent by the user or application, not automatically, implicitly executed by server.

Sure, but 'automatically' could be read in another way as well:

'When I create a *tem*porary table using CREATE TEMPORARY TABLE the server automatically creates a temporary table for me on disk'

For someone who is very versed in IT, it may be clear that this is not what is meant, but not so for a less experienced user. And, the original statement does not exclude this possibility.

----

So, IMO, adding the word 'internal' and the 'CREATE TEMPORARY TABLE' clarification makes this statement clearer & unambiguous.

I mostly agree with Valeriy, but will make some changes.

"Suggested fix:
Created_tmp_disk_tables 
'The number of internal temporary tables on disk created automatically by the server
while executing statements. This does not include TEMPORARY tables like those created
with CREATE TEMPORARY TABLE.'"

This is unnecessarily restrictive; perhaps the server in effect does the same thing when it creates internal tables. I'll add "internal," though.

"Created_tmp_tables 
'The number of internal and in-memory temporary tables created automatically by the
server while executing statements. This does not include TEMPORARY tables like those
created with CREATE TEMPORARY TABLE.'"

Here, "internal and in-memory" sounds like two sets of tables. But a different problem here is that the description is just wrong. This variable applies to all internal tables, not just internal in-memory tables. I'll correct that.

"Sure, but 'automatically' could be read in another way as well:

'When I create a *tem*porary table using CREATE TEMPORARY TABLE the server automatically
creates a temporary table for me on disk'"

That's like saying, "When I do X by means Y, the server automatically does X." But for "automatically" to mean "what I explicitly instructed the server to do" is to make it mean the opposite of what it means.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.