Search code examples
mysqltemp-tables

Dropping Temp Table At End Of Stored Procedure In MySQL

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;
Solution

  • 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