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

Hello, I am a senior architect. In a recent project where a master device frequently collects data from a slave device, the high reporting frequency and large data volume caused rapid disk consumption.

To save costs we perform regular backups and delete table records with DELETE . However, after executing DELETE the table file size does not shrink, which is confusing.

MySQL Data Structure

MySQL InnoDB uses a B+‑tree index and stores data in fixed‑size pages. When rows are deleted there are two possible outcomes:

Delete some records within a data page.

Delete an entire data page.

Why Table File Size Remains Unchanged

In InnoDB a delete operation only marks the record as free space; the physical page remains in the .ibd file. The freed space can be reused for future inserts, so the on‑disk file size does not decrease.

When an entire page is marked as deleted, the page is also kept for reuse. Therefore, whether a single row or a whole page is deleted, the file size stays the same.

How to Reduce Table Size

The DELETE statement does not reorganize the data file. To reclaim unused space you can run:

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;

OPTIMIZE TABLE is essentially a REBUILD + ANALYZE , while ALTER TABLE … ENGINE=INNODB performs a full table recreation.

Online DDL

DDL operations traditionally lock tables, causing “Waiting for meta data lock” problems. Since MySQL 5.6, Online DDL allows most DDL statements to be executed without blocking reads or writes.

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 – perform the change directly on the original table.

COPY – create a temporary table, copy data, then replace the original (requires extra disk space and blocks DML).

DEFAULT – let MySQL choose, preferring INPLACE when possible.

LOCK options

SHARE – table can be read but not written.

NONE – no restrictions; table can be read and written.

EXCLUSIVE – table cannot be read or written.

DEFAULT – MySQL decides the appropriate lock when the clause is omitted.

If ALGORITHM is omitted, MySQL automatically selects INSTANT, INPLACE, or COPY in that order. Specifying an unsupported algorithm results in an error.

Both OPTIMIZE TABLE and ALTER TABLE … ENGINE=INNODB support Online DDL, but it is still advisable to run them during low‑traffic periods.

Summary

When DELETE removes rows, MySQL only marks them as reusable; the table’s physical size does not shrink. To actually reduce the file size you need to rebuild the table using OPTIMIZE TABLE or ALTER TABLE … ENGINE=INNODB . Since MySQL 5.6 these operations can be performed with Online DDL to avoid long‑lasting table locks, though they should still be scheduled for off‑peak times.