Starting with the InnoDB Plugin, when the user requests to
TRUNCATE a table that is stored in an
.ibd file of its own (because
innodb_file_per_table was enabled when the table was created),
and if the table is not referenced in a
FOREIGN KEY constraint,
the InnoDB Plugin will drop and re-create the table in a new
.ibd file. This operation is much faster than
deleting the rows one by one, and will return disk space to the
operating system and reduce the size of page-level backups.
Previous versions of InnoDB would re-use the existing
.ibd file, thus releasing the space only to
InnoDB for storage management, but not to the operating system.
Note that when the table is truncated, the count of rows affected
TRUNCATE command is an arbitrary number.
Note: if there are referential constraints between the table being
truncated and other tables, MySQL instead automatically converts
TRUNCATE command to a
DELETE command that operates row-by-row, so
ON DELETE operations can occur on