INNODB_TEMP_TABLE_INFO

InnoDB temporary tables that are currently active in an InnoDB instance are described in the table INNODB TEMP TABLE INFO. The internal InnoDB temporary tables utilized by the optimizer are not disclosed. When a query is first made, a table called INNODB TEMP TABLE INFO is generated; it only resides in memory and is not stored on disk.

In other words – users can view a snapshot of the current InnoDB temporary tables in the INNODB TEMP TABLE INFO table. With the exception of optimized temporary tables utilized by InnoDB internally, the table contains metadata about all user- and system-created temporary tables that are active within a certain InnoDB instance.

Use the following query to see the temporary tables created along with their consumption and active state.

SELECT
   a.TABLE_ID,
   a.NAME,
   a.N_COLS,
   a.SPACE,
   b.ID,
   b.PATH,
   format_bytes(SIZE),
   b.STATE,
   b.PURPOSE
FROM information_schema.INNODB_TEMP_TABLE_INFO a
LEFT JOIN information_schema.INNODB_SESSION_TEMP_TABLESPACES b
ON a.SPACE = b.SPACE;

INNODB_TEMPORARY_TABLES

Hope you find this article helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s