TRUNCATE TABLE on MySQL InnoDB databases

Having come up against the extremely poor performance of using TRUNCATE instead of DELETE on MySQL InnoDB tables (see previous post MySQL Truncate slow performance problems) I thought I better come up with a solution that didn't mean leaving a table to clear for an hour.

The solution is to use a combination of SHOW CREATE and DROP. DROPping a table is very quick indeed, so as long as you have the CREATE code to hand then it's a simple matter to empty a table. The main thing to watch out for with InnoDB tables is foreign key constraints which are easily disabled.

Some sample code to use this from within PHP is shown below

function truncateTable($tableName)
{
   //Grab the code to create the table
   $sql = "show create table " . $tableName;
   $dataSet = DataHandler::loggedDbQuery($sql);
   $result = $dataSet->fetchRow();
   $createSQL = $result["Create Table"] . ";
        SET FOREIGN_KEY_CHECKS=1;";
   //Drop the table. We have to disable foreign key
   //checks, which means running the whole thing
   //from the command line
   $sql = "SET FOREIGN_KEY_CHECKS=0;
      drop table " . $tableName . ";".
      $createSQL;
   DataHandler::multipleDbQueries($sql);
}

This is used in conjunction with a static method I've created to run a standard (single) SQL query from within PHP called DataHandler::loggedDbQuery (which works with PearDB, which is where the fetchRow() method comes from) and a multi-line query function I have developed and wrote about in Multiple SQL queries using MySQL and PHP and referred to as DataHandler::multipleDbQueries($sql).

Link to this post

Comments:

If you had an admin tool, similiar to navicat it automates a great deal of this for you. navicat I literally select a table.. hit design save the design as the new table and drop the old. Its instant. Hope that helps. Also does synchronization and all sorts of nerd-stuff ;)

posted by Anonymous Ryan Turner : September 02, 2005 1:23 PM

Navicat doesn't seem to solve the problem of truncating within code at runtime, though. It appears to be a database design/editing tool whereas I was having the problem of deleting data at runtime and having it take so long on an InnoDB database.

posted by Blogger Karl Bunyan : September 02, 2005 1:29 PM

Post a Comment