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

In MySQL, executing a DELETE statement removes rows logically but does not shrink the underlying table file because InnoDB marks the space as reusable rather than physically freeing it.

MySQL Data Structure

InnoDB uses a B+‑tree index-organized table where data is stored in fixed‑size pages. Deleting data can either remove individual records within a page or mark an entire page as free.

Why Table Size Remains Unchanged

When a row such as the example record R4 is deleted, InnoDB simply flags the record as reusable. Subsequent inserts that fit the flagged range will reuse that space, so the on‑disk file size stays the same.

Similarly, when an entire page is cleared, the page is marked as free and can be reused later; the file size does not shrink.

How to Reduce Table Size

The DELETE operation does not reorganize the file. To reclaim unused space you can run OPTIMIZE TABLE table_name; , which rebuilds the table and compacts the data.

OPTIMIZE TABLE table_name;

Note: OPTIMIZE TABLE works for MyISAM, BDB, and InnoDB tables.

Alternatively, you can 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, allowing the operation to run with minimal locking.

Online DDL

DDL statements traditionally lock tables, causing “Waiting for meta data lock” issues. Since MySQL 5.6, Online DDL lets you perform many schema changes without exclusive locks.

Online DDL uses the ALGORITHM and LOCK clauses. Example:

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

ALGORITHM Options

INPLACE – performs the change directly on the original table.

COPY – creates a temporary copy, applies the change, then swaps tables (requires double disk space).

DEFAULT – lets MySQL choose, preferring INPLACE when possible.

LOCK Options

SHARE – table can be read but not written during the change.

NONE – no restrictions; reads and writes are allowed.

EXCLUSIVE – table cannot be read or written.

DEFAULT – MySQL decides the appropriate lock level.

If ALGORITHM is omitted, MySQL automatically selects the best method (INSTANT, INPLACE, or COPY). If the chosen algorithm is unsupported, the statement fails.

Summary

Deleting rows only marks them as reusable, so the table’s disk footprint does not shrink. To actually reduce size, rebuild the table using OPTIMIZE TABLE or ALTER TABLE … ENGINE=INNODB . Since MySQL 5.6, these operations can be performed online, but it is still advisable to run them during low‑traffic periods.