28

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.

simhumileco
  • 31,877
  • 16
  • 137
  • 115
Nate Weiner
  • 805
  • 2
  • 9
  • 15

2 Answers2

31

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.
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 6
    And if you specify the `blackhole` engine it will be stored in special device: /dev/null. – Johan Sep 26 '11 at 20:31
  • 2
    Well, they won't be called 'temp_table_innodb' on disk because the temporary table is only accessible by this one connection, such that if you make two separate connections to the db and make a temporary table with each, you can call them the same thing and they won't collide with each other. That all said, I have no idea whether they are in memory or on disk. I'd guess, but I'd no doubt guess wrong, so I won't. – Kenny Sep 26 '11 at 20:50
  • @Kenny: I found them... in the temp folder (of course!). See updated answer. – Mark Byers Sep 26 '11 at 20:51
  • 1
    OK, I found it. They are indeed stored on disk, and if you look in your my.cnf file, look for the tmpdir key. In a Debian default mysql installation, this appears to be set to /tmp so I created a temporary table and in a separate terminal, went to look in /tmp and lo and behold, there was an frm, myd and myi file there. Clearly, the names of these files were radically different to the name of the temporary table (for the reason I stated in my last comment), but they were there. [and I see you also found this while I was investigating; glad we came to the same conclusion :)] – Kenny Sep 26 '11 at 20:59
6

Like Mark said, it depends on what ENGINE you tell it to use. If you don't give an ENGINE, it will do "something", but not necessarily keep it just in memory. If you want to force the table to be in memory, you have to define it explicitly:

CREATE TEMPORARY TABLE foobar (id int) ENGINE=MEMORY;

However, the use of MEMORY-engine is restricted. For more information have a look at Internal Temporary Table Use in MySQL.

simhumileco
  • 31,877
  • 16
  • 137
  • 115
Alexander Janssen
  • 1,594
  • 13
  • 13
  • 2
    Note that (at least in MySQL 5.1.63) user created memory temp tables are limited by max_heap_table_size and unlike internal temp tables, will NOT be converted to on-disk temp tables if/when they grow too big. Inserting more data will result in an error. – Scott McCammon Aug 29 '12 at 17:44
  • 3
    above link is for internal temporary table(generated to execute some sql query) not for explicit temporary table. – Vishal Kumar Sep 04 '13 at 19:33