TRUNCATE TABLE Reclaims Space

When you 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 table is dropped and re-created in a new .idb file. This operation is much faster than deleting the rows one by one. The operating system can reuse the disk space, in contrast to tables within the InnoDB system tablespace, where only InnoDB can use the space after they are truncated. Page-level backups can also be smaller, without big blocks of unused space in the middle of the system tablespace.

Previous versions of InnoDB would re-use the existing .idb 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 statement is an arbitrary number.


If there is a referential constraint between columns of the table being truncated, it still is truncated using this fast technique.

If there are referential constraints between the table being truncated and other tables, the truncate operation fails. This is a change to the previous behavior, which would transform the TRUNCATE operation to a DELETE operation that would remove all the rows and trigger ON DELETE operations on child tables.

Copyright © 2010-2022 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout