Search code examples
mysqlperformancetemp-tables

Slow DROP TEMPORARY TABLE

Ran into an interesting problem with a MySQL table I was building as a temporary table for reporting purposes.

I found that if I didn't specify a storage engine, the DROP TEMPORARY TABLE command would hang for up to half a second.

If I defined my table as ENGINE = MEMORY this short hang would disappear.

As I have a solution to this problem (using MEMORY tables), my question is why would a temporary table take a long time to drop? Do they not use the MEMORY engine by default? It's not even a very big table, a couple of hundred rows with my current test data.

Solution

  • Temporary tables, by default, will be created where ever the mysql configuration tells it to, typically /tmp or somewhere else on a disk. You can set this location (and even multiple locations) to a RAM disk location such as /dev/shm.

    Hope this helps!