Why MySQL Table Size Does Not Decrease After DELETE and How to Reduce It

Problem Description

In a project where a master device frequently reports large amounts of data to a slave, the rapid growth of the MySQL tables caused disk usage to increase sharply. Although records are periodically deleted with DELETE, the size of the .ibd file does not shrink, which is confusing.

MySQL Data Structure

MySQL InnoDB stores rows in pages organized as a B+ tree. Deleting rows can affect either individual records within a page or an entire page.

Delete some records in a data page

Delete the whole data page

Why Table Size Remains Unchanged

When a row is deleted, InnoDB only marks the space as reusable; the physical page is not removed. The file size therefore stays the same until the space is reclaimed.

Both record‑level deletion and page‑level deletion only set a “deleted” flag, so the .ibd file does not shrink.

How to Reduce Table Size

Use OPTIMIZE TABLE table_name; to rebuild the table, reclaim unused pages and defragment the file. This works for MyISAM, BDB, and InnoDB tables.

Alternatively, rebuild the table with ALTER TABLE table_name ENGINE=INNODB; .

Online DDL

From MySQL 5.6 onward, Online DDL allows DDL operations without locking the table for 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 options: INPLACE, COPY, DEFAULT.

LOCK options: SHARE, NONE, EXCLUSIVE, DEFAULT.

If ALGORITHM is omitted, MySQL chooses the best method automatically.

Summary

DELETE only marks rows as reusable, so the table file does not shrink. To actually reduce the file size, rebuild the table with OPTIMIZE TABLE or ALTER TABLE, preferably during low‑traffic periods; Online DDL can further minimize impact.