Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.4Mb
PDF (A4) - 39.4Mb
PDF (RPM) - 38.7Mb
HTML Download (TGZ) - 11.1Mb
HTML Download (Zip) - 11.1Mb
HTML Download (RPM) - 9.8Mb
Man Pages (TGZ) - 213.4Kb
Man Pages (Zip) - 322.5Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

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

24.32.25 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

The INNODB_TEMP_TABLE_INFO table provides information about user-created InnoDB temporary tables that are active in an InnoDB instance. It does not provide information about internal InnoDB temporary tables used by the optimizer. The INNODB_TEMP_TABLE_INFO table is created when first queried, exists only in memory, and is not persisted to disk.

For usage information and examples, see Section 14.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.

The INNODB_TEMP_TABLE_INFO table has these columns:

  • TABLE_ID

    The table ID of the temporary table.

  • NAME

    The name of the temporary table.

  • N_COLS

    The number of columns in the temporary table. The number includes three hidden columns created by InnoDB (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR).

  • SPACE

    The ID of the temporary tablespace where the temporary table resides. In 5.7, non-compressed InnoDB temporary tables reside in a shared temporary tablespace. The data file for the shared temporary tablespace is defined by the innodb_temp_data_file_path system variable. By default, there is a single data file for the shared temporary tablespace named ibtmp1, which is located in the data directory. Compressed temporary tables reside in separate file-per-table tablespaces located in the temporary file directory defined by tmpdir. The temporary tablespace ID is a nonzero value that is dynamically generated on server restart.

  • PER_TABLE_TABLESPACE

    A value of TRUE indicates that the temporary table resides in a separate file-per-table tablespace. A value of FALSE indicates that the temporary table resides in the shared temporary tablespace.

  • IS_COMPRESSED

    A value of TRUE indicates that the temporary table is compressed.

Example

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

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

Notes

  • This table is useful primarily for expert-level monitoring.

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

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.