MySQL Bugs: #56812: DESCRIBE statement creates a temporary table on disk

Bug #56812	DESCRIBE statement creates a temporary table on disk
Submitted:	16 Sep 2010 8:41	Modified:	22 Sep 2010 19:58
Reporter:	Mihail Milushev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S5 (Performance)
Version:	5.1.41, 5.1.50	OS:	Linux (5.1.41-3ubuntu12.6-log (Ubuntu))
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Executing a DESCRIBE statement results in creation of on-disk temporary table.

How to repeat:
mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> describe some_table;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
.....
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

Suggested fix:
Retrieving schema information should not write data to disk.

Actually, this is expected, because Default column in DESCRIBE results is a BLOB:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 --column-
type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.50-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> desc t1;
Field   1:  `Field`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 1
Decimals:   0
Flags:      NOT_NULL

Field   2:  `Type`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  utf8_general_ci (33)
Length:     589815
Max_length: 7
Decimals:   0
Flags:      NOT_NULL BLOB

Field   3:  `Null`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     9
Max_length: 3
Decimals:   0
Flags:      NOT_NULL

Field   4:  `Key`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     9
Max_length: 0
Decimals:   0
Flags:      NOT_NULL

Field   5:  `Default`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  utf8_general_ci (33)
Length:     589815
Max_length: 0
Decimals:   0
Flags:      BLOB

Field   6:  `Extra`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     81
Max_length: 0
Decimals:   0
Flags:      NOT_NULL

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

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

"Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

    - Presence of a BLOB or TEXT column in the table"

temporary table can not be created in memory. 

This is expected and documented behavior.

This is hardly "documented behavior". Nowhere in the manual it is mentioned that DESCRIBE requires a temporary table at all (and to me at least this is an implementation detail, so it is not common sense to assume that it does), or that it returns a BLOB column that would force that temporary table to be created on disk, which is not even obvious in everyday use. Yes, if you're familiar with the internals of the server it might be obvious to you, but still that's far from "documented behavior". The "How MySQL Uses Internal Temporary Tables" chapter contains a pretty explicit list of conditions that result in on-disk temporary tables, and none of them even remotely suggests that DESCRIBE is a candidate — there are no ORDER BY, GROUP BY or DISTINCT clauses, and no SQL_SMALL_RESULT option, and you cannot use EXPLAIN as suggested.

I agree that we have a valid documentation request here.

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.

Added to temporary-table section:

The DESCRIBE statement uses BLOB as the type for some of its columns,
thus the temporary table used for the results is an on-disk table.