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  /  ...  /  InnoDB INFORMATION_SCHEMA Temporary Table Info Table

14.15.7 InnoDB INFORMATION_SCHEMA Temporary 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.

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';
+---------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_TEMP%) |
+---------------------------------------------+
| INNODB_TEMP_TABLE_INFO                      |
+---------------------------------------------+

For the table definition, see Section 24.32.26, “The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table”.

Example 14.12 INNODB_TEMP_TABLE_INFO

This example demonstrates characteristics of the INNODB_TEMP_TABLE_INFO table.

  1. Create a simple InnoDB temporary table:

    mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
  2. Query INNODB_TEMP_TABLE_INFO to view the temporary table metadata.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
    *************************** 1. row ***************************
                TABLE_ID: 194
                    NAME: #sql7a79_1_0
                  N_COLS: 4
                   SPACE: 182
    PER_TABLE_TABLESPACE: FALSE
           IS_COMPRESSED: FALSE

    The TABLE_ID is a unique identifier for the temporary table. The NAME column displays the system-generated name for the temporary table, which is prefixed with #sql. The number of columns (N_COLS) is 4 rather than 1 because InnoDB always creates three hidden table columns (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR). PER_TABLE_TABLESPACE and IS_COMPRESSED report TRUE for compressed temporary tables. Otherwise, these fields report FALSE.

  3. Create a compressed temporary table.

    mysql> CREATE TEMPORARY TABLE t2 (c1 INT) ROW_FORMAT=COMPRESSED ENGINE=INNODB;
  4. Query INNODB_TEMP_TABLE_INFO again.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
    *************************** 1. row ***************************
                TABLE_ID: 195
                    NAME: #sql7a79_1_1
                  N_COLS: 4
                   SPACE: 183
    PER_TABLE_TABLESPACE: TRUE
           IS_COMPRESSED: TRUE
    *************************** 2. row ***************************
                TABLE_ID: 194
                    NAME: #sql7a79_1_0
                  N_COLS: 4
                   SPACE: 182
    PER_TABLE_TABLESPACE: FALSE
           IS_COMPRESSED: FALSE

    PER_TABLE_TABLESPACE and IS_COMPRESSED report TRUE for the compressed temporary table. The SPACE ID for the compressed temporary table is different because compressed temporary tables are created in separate file-per-table tablespaces. Non-compressed temporary tables are created in the shared temporary tablespace (ibtmp1) and report the same SPACE ID.

  5. Restart MySQL and query INNODB_TEMP_TABLE_INFO.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
    Empty set (0.00 sec)

    An empty set is returned because INNODB_TEMP_TABLE_INFO and the data within it are not persisted to disk on server shutdown.

  6. Create a new temporary table.

    mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
  7. Query INNODB_TEMP_TABLE_INFO to view the temporary table metadata.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
    *************************** 1. row ***************************
                TABLE_ID: 196
                    NAME: #sql7b0e_1_0
                  N_COLS: 4
                   SPACE: 184
    PER_TABLE_TABLESPACE: FALSE
           IS_COMPRESSED: FALSE

    The SPACE ID is new because it is dynamically generated on server restart.



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.