Dropping Temp Table At End Of Stored Procedure In MySQL

mysql temp-tables
Mike Flynn picture Mike Flynn · Aug 6, 2012 · Viewed 15.4k times · Source

Do I need to add DROP TEMPORARY TABLE IF EXISTS data; at the end of the stored procedure even though I have the check at the top? Is there a performance implication?

CREATE DEFINER=`TEST`@`%` PROCEDURE `TEST`() BEGIN

    DROP TEMPORARY TABLE IF EXISTS data;

    CREATE TEMPORARY TABLE data AS 
...


END;

Answer

EkoostikMartin picture EkoostikMartin · Aug 6, 2012

In MySQL, temporary tables are dropped automatically when a database connection is closed. If you plan on leaving your connection open after the stored procedure, your temp table will exist on disk until that connection is closed. The performance implications depend on many factors, such as how you have configured temporary table storage on your server, how much data is in the table, etc.

It is considered best practice to just drop the temp table as soon as you are done with it. Then you save yourself the worry about those potential performance implications all together

Related questions

Create a temporary table in a SELECT statement without a separate CREATE TABLE

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I …

Read More
How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to …

Read More
Create a temporary table in MySQL with an index from a select

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use ALTER TABLE because this causes an implicit commit. Therefore I'm looking for the syntax to add …

Read More