Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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

24.36.28 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

The INNODB_TEMP_TABLE_INFO table 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 first queried, exists only in memory, and is not persisted to disk.

For usage information and examples, see Section 15.14.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. All 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. The SPACE ID for the temporary tablespace is always a nonzero value and is dynamically generated on server restart.

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: 97
    NAME: #sql8c88_43_0
  N_COLS: 4
   SPACE: 76

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.