Efficiently Truncate InnoDB Tables

I was sent a great article today on performance tuning for MySQL’s InnoDB Storage Engine.
I thought the last point was particularly interesting – I always thought that a TRUNCATE TABLE was always better than a DELETE FROM as it can just drop all the data without looking at each individual row – apparently that’s not the case and they’re pretty much equivalent for InnoDB.

So I wrote a store procedure to allow a efficient truncating of InnoDB tables, which just creates a temporary structure copy of the table, drops the original, then renames the temp table back to the original name.

delimiter //
DROP PROCEDURE IF EXISTS INNODB_TRUNCATE//
CREATE PROCEDURE INNODB_TRUNCATE(IN tbl CHAR(128))
BEGIN
 
SET @tbl_tmp  = CONCAT(tbl, '_idbtrunc_tmp');
SET @tbl   = tbl;
 
SET @v = CONCAT("DROP TABLE IF EXISTS ", @tbl_tmp);
PREPARE drop_tmp FROM @v;
EXECUTE drop_tmp;
 
SET @v = CONCAT("CREATE TABLE ", @tbl_tmp, " LIKE ", @tbl);
PREPARE create_tmp FROM @v;
EXECUTE create_tmp;
 
SET @v = CONCAT("DROP TABLE ", @tbl);
PREPARE drop_tbl FROM @v;
EXECUTE drop_tbl;
 
SET @v = CONCAT("RENAME TABLE ", @tbl_tmp, " TO ", @tbl);
PREPARE rename_tmp FROM @v;
EXECUTE rename_tmp;
 
END;
//
delimiter ;

Use:

> CALL INNODB_TRUNCATE('my_innodb_table');
Affected rows: 0
Time: 0.218ms