MYSQL Temporary Tables - How to view active ones

Question

We have a simple "crm-like" software in our company. There are some complex queries that were taking some time, daily-use queries... so i am testing some modifications to use Temporary Tables to replace all the complex joins and subqueries we need.

So far going really well, got a 90%+ speed.

Since its a web app (codeigniter + mysql), i plan to put it in a "production-test" enviroment so 50% of the users can help me test it. I would like to monitor the tables that are active, and if possible for each connection.

My question is - Is there any way i can view all the TEMPORARY TABLES that are active in the mysql instance? Maybe view its data?

I read something about a PLUGIN or something like, but the article was far to messy and i coudnt understand.

Thanks alot and sorry for my english - not my native lang.

Answer 1

temp table = temporary, it deleted right after the query

there is no direct solution, except logging all the queries and execute one-by-one to exam which query require tmp_table

the system variables like Created_tmp_tables might give some ideas

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

Answer 2

One of the problems of temporary tables is actually tracking the usage, then there's the overhead of creating, indexing and deleting them - particularly with a web application where the lifetime of the temporary table is usually as long as the lifetime of the HTTP request.

Where pre-compiled results (i.e. materialized views) will be of benefit, I set up a conventional table, adding field(s) which reference the MySQL connection id / the web session id / the source query + time generated depending on the TTL for the data and whether it will be shared or not.

In addition to getting detailled tracking of the usage of the table, it makes it much easier to address query tuning and of course, the schema is better documented.