14.9.5 Reclaiming Disk Space with TRUNCATE TABLE

To reclaim operating system disk space when
truncating an
InnoDB table, the table must be stored in its
own .ibd file. For a table to
be stored in its own .ibd
file, innodb_file_per_table must
enabled when the table is created. Additionally, there cannot be a
foreign key constraint
between the table being truncated and other tables, otherwise the
TRUNCATE TABLE operation fails. A foreign key
constraint between two columns in the same table, however, is
permitted.

When a table is truncated, it is dropped and re-created in a new
.ibd file, and the freed space is returned to
the operating system. This is in contrast to truncating
InnoDB tables that are stored within the
InnoDBsystem tablespace
(tables created when
innodb_file_per_table=OFF), where only
InnoDB can use the freed space after the table
is truncated.

The ability to truncate tables and return disk space to the
operating system also means that
physical backups can
be smaller. Truncating tables that are stored in the system
tablespace (tables created when
innodb_file_per_table=OFF) leaves blocks of
unused space in the system tablespace.