Why MySQL DELETE Doesn’t Shrink Table Size and How to Fix It

In a project where a host machine frequently receives large amounts of data from a client, the MySQL tables grew quickly and deleting rows did not reduce the file size.

MySQL InnoDB stores data in pages using a B+‑tree index. Deleting rows only marks the records or whole pages as reusable; the physical .ibd file size remains unchanged.

MySQL data structure

Deletion can affect:

individual records on a data page

entire data pages

Why the table file size stays the same

Deleted rows are merely marked as free space for future inserts, so the on‑disk size does not shrink.

How to reduce the table size

Use OPTIMIZE TABLE table_name; to reclaim unused space and defragment the file (effective for MyISAM, BDB, and InnoDB tables). OPTIMIZE TABLE table_name; Alternatively, rebuild the table with

ALTER TABLE table_name ENGINE=INNODB;

ALTER TABLE table_name ENGINE=INNODB

Both commands support online DDL in MySQL 5.6 and later, but it is advisable to run them during low‑traffic periods.

Online DDL

DDL operations can lock tables. Since MySQL 5.6, Online DDL allows concurrent reads and writes. The ALGORITHM and LOCK clauses control the execution mode.

Example:

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

ALGORITHM : INPLACE (modify in place), COPY (create temporary table), DEFAULT (MySQL chooses).

LOCK : SHARE (read‑only), NONE (read/write), EXCLUSIVE (no access), DEFAULT (MySQL decides).

In summary, DELETE only marks rows as reusable, so the table space does not shrink; use OPTIMIZE TABLE or ALTER TABLE … ENGINE=INNODB to reclaim space, and prefer online DDL options for minimal disruption.