15.11.5 Reclaiming Disk Space with TRUNCATE TABLE 15.12 InnoDB and Online DDL 3175 Reclaiming Disk Space with TRUNCATE TABLE Another way to perform a defragmentation operation is to use  mysqldump  to dump the table to a text file, drop the table, and reload it from the dump file. If the insertions into an index are always ascending and records are deleted only from the end, the  InnoDB filespace management algorithm guarantees that fragmentation in the index does not occur. 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  InnoDB system tablespace (tables created when  innodb_file_per_table=OFF ) and tables stored in shared general tablespaces, 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 ) or in a general tablespace leaves blocks of unused space in the tablespace. The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Benefits of this feature include: ALGORITHM=INSTANT  support is available for  ADD COLUMN  and other operations in MySQL 8.0.12. Note Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation instantly or in place, as permitted, with as little locking as possible. You can control aspects of a DDL operation using the  ALGORITHM  and  LOCK  clauses of the  ALTER TABLE statement. These clauses are placed at the end of the statement, separated from the table and column specifications by commas. For example: ALTER TABLE  tbl_name  ADD PRIMARY KEY ( column ), ALGORITHM=INPLACE, LOCK=NONE; The  LOCK  clause may be used for operations that are performed in place and is useful for fine-tuning the degree of concurrent access to the table during operations. Only  LOCK=DEFAULT  is supported for operations that are performed instantly. The  ALGORITHM  clause is primarily intended for performance comparisons and as a fallback to the older table-copying behavior in case you encounter any issues. For example: •Less disk space usage and I/O overhead than the table-copy method. •For in-place operations, the ability to adjust the balance between performance and concurrency during DDL operations using the  LOCK  clause. See The LOCK clause. •Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.