I noticed this post: http://forums.mysql.com/read.php?10,588192,588192#msg-588192 and I first thought of a different way to handle the situation.
If you need tables to handle temporary information you can go about it in two ways. One if it is per session processing then you should create a TEMPORARY table only:
CREATE TEMPORARY TABLE `temporary_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ;
This will result in NO .frm file and will go away and close of the session.
If you need it available longer and need it to be fast you can use a MEMORY table. This will stay until you reboot database, delete table, etc...
CREATE TABLE `memory_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MEMORY;
This again means NO .frm file.
So if you want to go clean the memory tables up because you have so many or something you can find a list with the following ...
SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,
SUM(DATA_LENGTH+INDEX_LENGTH) AS size,
SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
AND ENGINE = "MEMORY" GROUP BY TABLE_SCHEMA, ENGINE;
As always... review your needs and benchmark what works best for you and your application.
