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

In a project where a host machine frequently receives large amounts of data from a lower‑level device, the MySQL database grows rapidly; although DELETE statements remove rows, the physical table file size does not decrease.

MySQL Data Structure

InnoDB stores data using a B+‑tree index and organizes rows into fixed‑size pages. When rows are deleted, InnoDB either marks individual records within a page as free or marks an entire page as free, but it does not immediately release the page back to the operating system.

Why Table Size Remains Unchanged

For example, deleting record R4 simply flags that slot as reusable; future inserts that fit the same key range will reuse the space. Even when an entire page is cleared, the page remains part of the .ibd file, so the file size stays the same.

How to Reduce Table Size

To actually shrink the table, you must rebuild it or reclaim unused space. The common commands are:

OPTIMIZE TABLE table_name;

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

Alternatively, you can rebuild the table with:

ALTER TABLE table_name ENGINE=INNODB;

Online DDL

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

When using online DDL, you can specify ALGORITHM and LOCK options, for example:

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

ALGORITHM options

INPLACE – execute the change directly on the original table.

COPY – create a temporary copy, apply the change, 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 level.

If ALGORITHM is omitted, MySQL automatically selects the best method (INSTANT, INPLACE, or COPY). Both OPTIMIZE TABLE and ALTER TABLE … ENGINE=INNODB support online DDL, but it is advisable to run them during low‑traffic periods.

Summary

Deleting rows in InnoDB only marks them as reusable, so the table file size does not shrink; to reclaim space you must rebuild the table using OPTIMIZE TABLE or ALTER TABLE … ENGINE=INNODB . Since MySQL 5.6, these operations can be performed online, though they should be scheduled during off‑peak hours.