I had this patch for a while where one can get listing of both session and global temporary tables across all sessions. It really helped lot of times to understand the bottlenecks of some of the temporary table issues as MySQL never exposed them in the form of SHOW TABLES.

I also added a new status variable called ‘Created_tmp_heap_to_disk_tables‘, which keeps track of how many memory based temp tables are re-created back to disk based.

The patch is now ported to newer MySQL versions, both 5.0 and 5.1; and it works great on most of the platforms that I tested (Mac, Linux and Windows)

It introduces two new INFORMATION_SCHEMA tables, TEMPORARY_TABLES and GLOBAL_TEMPORARY_TABLES along with supporting regular SHOW syntax

 
SHOW [SESSION/GLOBAL] TEMPORARY TABLES [FROM db]

Some examples of how it works at present

Session Temp Tables

mysql> show session temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> show temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> select * from information_schema.temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          1 | test     | t2         | MEMORY | #sql29da_1_3 |
|          1 | test     | t1         | MyISAM | #sql29da_1_2 |
+------------+----------+------------+--------+--------------+
2 rows in set (0.00 sec)

Global Temp Tables Across All Sessions:

mysql> select * from information_schema.global_temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          6 | test     | t3         | MyISAM | #sql29da_6_0 |
|          5 | test     | t2         | MEMORY | #sql29da_5_3 |
|          5 | test     | t1         | MyISAM | #sql29da_5_2 |
|          4 | venu     | v1         | InnoDB | #sql29da_4_0 |
+------------+----------+------------+--------+--------------+
4 rows in set (0.00 sec)
 
mysql> show global temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  6 | test | t3    | MyISAM | #sql29da_6_0 |
|  5 | test | t2    | MEMORY | #sql29da_5_3 |
|  5 | test | t1    | MyISAM | #sql29da_5_2 |
|  4 | venu | v1    | InnoDB | #sql29da_4_0 |
+----+------+-------+--------+--------------+
4 rows in set (0.00 sec)
  • Id: Session ID
  • Db: Database Name
  • Engine: Engine Type
  • Name: Internal Name, how its stored in the file system

Will publish the patch once am done with porting internal temp tables listing by introducing two new columns Table_Type and Info where Table_Type will indicate if its internal or external and Info will have initial 1024 bytes of the query that caused the internal temp table.



Related posts:

  1. When indexes are created in internal temporary tables
  2. InnoDB show table status gets blocked
  3. How to improve subqueries derived tables performance
  4. MySQL MyISAM and Merge Tables Incompatibility – II