Are tables created with "CREATE TEMPORARY TABLE" in memory or on disk?

Question

In MySQL, when you create a temporary table, for example, CREATE TEMPORARY TABLE ..., is that table created and held in memory or on the disk?

I have read through the docs and Google'd it and have not come up with an answer.

Answer 1

It depends on what engine you specify. By default the table data will be stored on disk. If you specify the MEMORY engine, the data will only be stored in memory.

It should be possible to actually find the files that are created in the filesystem when the temporary tables are created. After running the following commands:

CREATE TABLE test.table_myisam (x int) ENGINE=MyISAM;
CREATE TABLE test.table_memory (x int) ENGINE=MEMORY;
CREATE TEMPORARY TABLE test.temp_table_myisam (x int) ENGINE=MyISAM;
CREATE TEMPORARY TABLE test.temp_table_memory (x int) ENGINE=MEMORY;

I then checked the directory: C:\ProgramData\MySQL\MySQL Server 5.5\data\test (on Windows) and the files present were:

table_innodb.frm   # Table definition.
table_innodb.MYD   # MyISAM table data file.
table_innodb.MYI   # MyISAM table index file.

table_memory.frm   # No MYD or MYI file for the MEMORY engine.

The temporary tables are stored in C:\Windows\Temp and have unusual names, but internally the data is stored in the same way.

#sql9a0_7_d.frm    # This is the MyISAM temporary table.
#sql9a0_7_d.MYD    # MyISAM data file for temporary table.
#sql9a0_7_d.MYI    # MyISAM index file for temporary table.

#sql9a0_7_c.frm    # This is the MEMORY engine file. No MYD or MYI.