MySQL Bugs: #70433: SHOW FULL COLUMNS FROM table

Bug #70433	SHOW FULL COLUMNS FROM table_name creates a disk temp table
Submitted:	26 Sep 2013 11:25	Modified:	26 Sep 2013 11:47
Reporter:	jia liu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.1.68 5.5.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	disk temp table, show full columns

Description:
when issue a SHOW FULL COLUMNS FROM table_name,mysql create a disk temp table.

dba@localhost : mysql 19:09:57> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 2     |
| Created_tmp_files       | 8     |
| Created_tmp_tables      | 39090 |
+-------------------------+-------+
3 rows in set (0.00 sec)

dba@localhost : mysql 19:09:58> SHOW FULL COLUMNS FROM user; 
......
42 rows in set (0.00 sec)

dba@localhost : mysql 19:10:06> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 3     |
| Created_tmp_files       | 8     |
| Created_tmp_tables      | 39096 |
+-------------------------+-------+
3 rows in set (0.00 sec)

It will cause problems on system which is io bound.
We hit this problem even on fusion io card.

How to repeat:
show global status like 'Created_tmp_disk_tables';
show global status like 'Created_tmp_disk_tables';
SHOW FULL COLUMNS FROM user;
show global status like 'Created_tmp_disk_tables';

Suggested fix:
Don't create disk temp table ,use in-memory temp table if this command really need  a temp tabl.

Thank you for the bug report.

SHOW FULL COLUMNS is based on INFORMATION_SCHEMA.COLUMNS.  If you do SHOW CREATE TABLE on that table, you will see that some of the columns are of type LONGTEXT.  MEMORY tables cannot have BLOB/TEXT columns.  Hence, a MyISAM table will have to be used here.  As far as I can see, this is intended behavior and not a bug.  Changing severity to S4 (Feature request).

This is a major issue on many installations where whatever connector/framework/code ends up being responsible for thousands of these calls per second.  It should be a worthwhile feature to see this implemented without a need for disk temporary table.

Bug #73743 marked as duplicate of this one

Øystein Grøvlen, Its is not necessary that only it TEXT/BLOB are present in SHOW COLUMN or EXPLAIN tablename than only it will create Disk Tmp Table.

Please the below case:

CREATE TABLE `TEST` (
  `id` int(11)
) ENGINE=InnoDB;

show status like 'Created_tmp_disk_tables';

EXPLAIN TEST;

show status like 'Created_tmp_disk_tables';

You can clearly see that even though INT can be inmemory still it went disk bound.

Flavian, EXPLAIN/DESCRIBE/SHOW COLUMNS will fetch information from information_schema.columns.  It is this table that contains TEXT columns.  The definition of the table that is described does not make any difference.