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

comments powered by Disqus