Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.3Mb
PDF (A4) - 35.3Mb
PDF (RPM) - 34.3Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 305.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

22.31.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

INNODB_TEMP_TABLE_INFO contains metadata about active InnoDB temporary tables. With the exception of optimized internal temporary tables used by InnoDB, INNODB_TEMP_TABLE_INFO reports on all user and system-created temporary tables that are active within a given InnoDB instance. The table is maintained in memory and not persisted to disk.

Prior to the introduction of the INNODB_TEMP_TABLE_INFO table in MySQL 5.7.1, InnoDB temporary table metadata was stored in InnoDB system tables.

For usage information and examples, see Section 15.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Information Table”.

Table 22.29 INNODB_TEMP_TABLE_INFO Columns

Column nameDescription
TABLE_IDThe table ID of the active temporary table.
NAMEThe name of the active temporary table.
N_COLSThe 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).
SPACEThe tablespace identifier (a numerical value) for the tablespace in which the temporary table resides. As of MySQL 5.7.1, all non-compressed InnoDB temporary tables reside in a shared temporary tablespace, as defined by innodb_temp_data_file_path. By default the shared temporary tablespace is named ibtmp1 and located in the data directory. Compressed temporary tables reside in separate per-table tablespaces located in the temporary file directory, as defined by tmpdir. The SPACE ID is always a non-zero value and is dynamically generated on server restart.
PER_TABLE_SPACEA value of TRUE indicates that the temporary table resides in a separate per-table tablespace. A value of FALSE indicates that the temporary table resides in the shared temporary tablespace.
IS_COMPRESSEDA value of TRUE indicates that the temporary table is compressed.

Example:

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 32
                NAME: #sqlaf56_2_0
              N_COLS: 4
               SPACE: 19
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE
1 row in set (0.00 sec)

Notes:

  • This table is primarily useful for expert level monitoring.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.


User Comments
Sign Up Login You must be logged in to post a comment.