TRUNCATE TABLE on MySQL InnoDB databases
4 October 2004, by Karl Bunyan
The main thing to
watch out for with InnoDB tables is foreign key constraints which are
easily disabled. 
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).
October 4, 2004 at 2:36 pm
Filed in: MySQL, Programming
No comments