K!

by Karl Bunyan

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.

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).

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 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 Karl Bunyan : September 02, 2005 1:29 PM