Why MySQL DELETE Does Not Reduce Table File Size and How to Shrink It

In a project that frequently uploads large amounts of data, the MySQL table size keeps growing even after rows are deleted, leading to high disk usage.

MySQL stores data in InnoDB using B+‑tree pages. Deleting rows only marks the records or whole pages as reusable; the physical file size remains unchanged because the space is not released.

Two deletion scenarios exist: removing individual records within a page or removing an entire data page. In both cases the space is merely marked as free for future inserts.

To actually reduce the table file size you must rebuild or reorganize the table. The common approaches are:

OPTIMIZE TABLE table_name;

OPTIMIZE TABLE works for MyISAM, BDB, and InnoDB tables and performs a recreate + analyze operation.

ALTER TABLE table_name ENGINE=INNODB;

ALTER TABLE forces a table recreation, which also releases unused space. The difference is that OPTIMIZE TABLE combines recreation with statistics analysis, while ALTER TABLE only recreates the table.

Since MySQL 5.6, Online DDL allows schema changes without locking the table for reads and writes. You can specify the algorithm and lock mode in the DDL statement, e.g.:

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

The ALGORITHM option can be INPLACE, COPY, or DEFAULT, and the LOCK option can be SHARE, NONE, EXCLUSIVE, or DEFAULT. Online DDL is recommended during low‑traffic periods.

In summary, DELETE only marks rows as reusable, so the table space does not shrink; to reclaim space you need to run OPTIMIZE TABLE or ALTER TABLE, and you can perform these operations online using the appropriate ALGORITHM and LOCK settings.