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;