Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 36.5Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

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

24.32.26 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

INNODB_TEMP_TABLE_INFO provides information about user-created InnoDB temporary tables that are currently active within the InnoDB instance. It does not provide information about internal InnoDB temporary 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”.

The INNODB_TEMP_TABLE_INFO table has these columns:

  • TABLE_ID

    The table ID of the active temporary table.

  • NAME

    The name of the active temporary table.

  • N_COLS

    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).

  • SPACE

    The tablespace identifier (a numerical value) for the tablespace where the temporary table resides. In 5.7, all 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 configuration option. 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, as defined by tmpdir. The SPACE ID for the temporary tablespace is always a nonzero value and is dynamically generated on server restart.

  • PER_TABLE_SPACE

    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;
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

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
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.