information about user-created
tables that are currently active within the
InnoDB instance. It does not provide
information about internal
tables that are used by the optimizer. The
INNODB_TEMP_TABLE_INFO table is
created when it is first queried and only exists in memory. It is
not persisted to disk.
For usage information and examples, see Section 14.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.
INNODB_TEMP_TABLE_INFO table has
The table ID of the active temporary table.
The name of the active temporary table.
The number of columns in the temporary table. The number always includes three hidden columns created by
InnoDB(DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR).
The tablespace identifier (a numerical value) for the tablespace where the temporary table resides. In 5.7, all non-compressed
InnoDBtemporary tables reside in a shared temporary tablespace. The data file for the shared temporary tablespace is defined by the
innodb_temp_data_file_pathconfiguration option. By default, there is a single data file for the shared temporary tablespace named
ibtmp1, which is located in the
datadirectory. Compressed temporary tables reside in separate file-per-table tablespaces located in the temporary file directory, as defined by
SPACEID for the temporary tablespace is always a nonzero value and is dynamically generated on server restart.
A value of
TRUEindicates that the temporary table resides in a separate file-per-table tablespace. A value of
FALSEindicates that the temporary table resides in the shared temporary tablespace.
A value of
TRUEindicates that the temporary table is compressed.
mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G *************************** 1. row *************************** TABLE_ID: 38 NAME: #sql26cf_6_0 N_COLS: 4 SPACE: 52 PER_TABLE_TABLESPACE: FALSE IS_COMPRESSED: FALSE