MySQL Temporary Tables

Submitted by admin on Mon, 07/13/2015 - 4:40pm

If you have ever needed a routine which rebuilt a large database table every night, you've probably run into the following situation:  while the table is being rebuilt, whatever scripts which read from it will be broken, and you risk users of your system (or site) seeing the data when its still only half-populated.

For those MySQL gurus out there, you're probably screaming "use transactions!"  But, if you are using a MyISAM table instead of InnoDB, that isn't an option.

So what to do?

Temporary tables to the rescue!

 

If you didn't know what this type of table is, it is a table created in memory, which MySQL will automatically kill when your connection ends.  For web sites, that means as soon as your script is finished running.

The idea is you would build up your large table as a temporary table, then, once finished, copy it to your "real" table.  The operation takes only a few seconds for hundreds of thousands of records.

Your code would look a little like this:

At the top of your script (in my case, PHP), create your temporary table:

mysql_query("
  CREATE TEMPORARY TABLE `temp_my_table` (
    `cwid` int(11) NOT NULL,
    `pidm` int(11) NOT NULL,
    `first_name` varchar(100) NOT NULL,
    `last_name` varchar(100) NOT NULL,

  .... and so on

    ");

Then, in your script, write to this table exactly as you would any other table.  Then, at the very end of your script, copy it to your real table:

mysql_query("TRUNCATE my_table");
mysql_query("INSERT my_table SELECT * FROM temp_my_table ");

I am truncating my "real" table first, but of course you would only do that if you need to for your situation.