Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.9Kb
Man Pages (Zip) - 189.5Kb
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.33.28 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 15.14.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.

Table 24.30 INNODB_TEMP_TABLE_INFO Columns

Column name Description
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 configuration option. 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;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
TABLE_ID: 97
    NAME: #sql8c88_43_0
  N_COLS: 4
   SPACE: 76
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.