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
