Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.5Mb
PDF (A4) - 37.5Mb
PDF (RPM) - 36.9Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 211.3Kb
Man Pages (Zip) - 321.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  InnoDB INFORMATION_SCHEMA Temporary Table Information Table

14.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Information Table

The INNODB_TEMP_TABLE_INFO table provides users with a snapshot of active InnoDB temporary tables. The table contains metadata about all user and system-created temporary tables that are active within a given InnoDB instance with the exception of optimized temporary tables that are used internally by InnoDB.

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.31.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 with a single column:

    mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
    Query OK, 0 rows affected (0.00 sec)
  2. Query the INNODB_TEMP_TABLE_INFO table to view the temporary table's 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 only report TRUE for compressed temporary tables.

  3. Create a compressed temporary table. Before you do so, ensure that innodb_file_format is set to Barracuda, which is required to create tables with a compressed row format.

    mysql> SET GLOBAL innodb_file_format="Barracuda";
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CREATE TEMPORARY TABLE t2 (c1 INT) ROW_FORMAT=COMPRESSED ENGINE=INNODB;
    Query OK, 0 rows affected (0.01 sec)
  4. Query the INNODB_TEMP_TABLE_INFO table again.

    mysql> CREATE TEMPORARY TABLE t2 (c1 INT) ROW_FORMAT=COMPRESSED ENGINE=INNODB;
    Query OK, 0 rows affected (0.01 sec)
    
    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 per-table tablespaces. Non-compressed temporary tables share a single tablespace (ibtmp1, by default) and report the same SPACE ID.

  5. Restart MySQL and query the INNODB_TEMP_TABLE_INFO table.

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

    An empty set is returned because the INNODB_TEMP_TABLE_INFO table 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;
    Query OK, 0 rows affected (0.00 sec)
  7. Query the INNODB_TEMP_TABLE_INFO table to view the temporary table's 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
Sign Up Login You must be logged in to post a comment.