MYSQL Temporary Tables - How to view active ones

mysql mysql-management temp-tables
ddutra picture ddutra · Aug 17, 2011 · Viewed 13.9k times · Source

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

ajreal picture ajreal · Aug 17, 2011

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     |
+-------------------------+-------+

Related questions

ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost'

I seem to be unable to re-create a simple user I've deleted, even as root in MySQL. My case: user 'jack' existed before, but I deleted it from mysql.user in order to recreate it. I see no vestiges of …

Read More
How to set root password to null

How can I change the password for root user of MySQL to null -- meaning no password or '' -- from the MySQL command line client?

Read More
mysql command for showing current configuration variables

Can not find a command that displays the current configuration of mysql from within the database. I know I could look at /etc/mysql/my.cnf but that is not what I need.

Read More