Understanding InnoDB Delete Behavior and Space Reclamation with OPTIMIZE TABLE

In InnoDB, a DELETE operation does not physically remove data; MySQL only marks the rows as deleted, so the table file size on disk does not shrink – a behavior often called "soft delete".

To verify this, the article creates a table t with three integer columns and indexes, then inserts 100,000 rows via a stored procedure.

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

The procedure testData loops from 1 to 100000, inserting each row, and is executed with CALL testData();. After insertion, querying the information_schema.tables shows the table occupies about 3.52 MB.

Running DELETE FROM t; removes all rows (100,000 affected) but a subsequent size check still reports 3.52 MB, confirming that the space was not released.

The article explains that deleted rows become "holes" that can be reused for future inserts (row‑record reuse) or, if an entire data page is marked empty, the whole page can be reused (page reuse). These holes increase the number of data pages read, causing extra disk I/O and slower queries.

To reclaim the wasted space, the article uses OPTIMIZE TABLE t;, which rebuilds the table by copying data to a temporary table and replacing the original. After optimization, the table size drops to about 0.02 MB, representing only the table definition.

Another method to achieve the same effect is ALTER TABLE t ENGINE=InnoDB;, which also forces a table rebuild.

Finally, the article notes that the behavior described applies to the InnoDB engine and may differ for other storage engines.