I was sent a great article
today on performance tuning for MySQL’s InnoDB
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.