8.4. TRUNCATE TABLE Reclaims Space

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 by the TRUNCATE command is an arbitrary number.

Note: if there are referential constraints between the table being truncated and other tables, MySQL instead automatically converts the TRUNCATE command to a DELETE command that operates row-by-row, so that ON DELETE operations can occur on child tables.

User Comments
Sign Up Login You must be logged in to post a comment.