Temporary Tables With MySQL

If you have had problems with MySQL not supporting functionality like sub selects this short article might be of interest for you. This article will explain how to use temporary tables with MySQL.

The juice

The SQL code below shows you how you can create a temporary table.

CREATE TEMPORARY TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP;
INSERT INTO TempTable VALUES( 1, “Foo bar” );
SELECT * FROM TempTable;
DROP TABLE TempTable;

If you’re using an MySQL version older than 3.23, you should use the code below.

CREATE TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP;
LOCK TABLE TempTable WRITE;
INSERT INTO TempTable VALUES( 1, “Foo bar” );
SELECT * FROM TempTable;
DROP TABLE TempTable;
UNLOCK TABLES;

Temporary tables

Temporary tables are per connection so heavy loaded sites will not have a problem with using the same table name since it’s unique per connection. If there already exists a table, which is not temporary, with the same same this table is then hidden until the temporary table is deleted. Temporary tables where added in MySQL version 3.23.

If you use an older version of MySQL than 3.23 you can’t use temporary tables, but you can use heap tables.

If the connection to the database is lost the temporary table is automatically deleted.

Heap tables

The heap tables are shared between all connections. Therefore you must use locking of the temporary table to ensure that no other connection is corrupting the data in your temporary table.

Since we need speed and the tables are temporary we create them as heap tables. This means that the tables are stored in memory and not on disk. Of course the data will be lost if the power is lost, but the kind of information stored in a heap table should be temporary data used for further queries.

To free memory always remember to drop the heap tables when you don’t need them anymore. Or at least delete the rows which is no longer of interest. Heap tables can take up a lot of memory if you don’t clean up after you’re done.

Heap tables are shared between all clients (just like any other table), so use them with care.

Using the LOCK TABLE mechanism

Since lock table works across connections this can be used to mimic the behaviour of the temporary tables. Although this is not that effective on servers with many connections, it works.

The lock table mechanism ensures that no other connections get access to write to the table so you don’t get corrupted data in the table while you are using it.

If the connection to the database is lost the locks are automatically unlocked.