How to Clean Up MySQL Table Fragmentation with OPTIMIZE and ALTER

Why MySQL tables become fragmented

When you use variable‑length column types such as VARCHAR, TEXT, or BLOB, deleting rows leaves empty space in the underlying storage. Over time, many deletions can cause the amount of free space to exceed the space actually used for data, leading to fragmentation.

Checking fragmentation

You can inspect each table’s size and free space with queries against information_schema.tables:

select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='db002';

select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='db002' and data_free !=0;

Defragmentation methods

For engines that support it, run OPTIMIZE TABLE on the target tables:

OPTIMIZE TABLE aaa_safe, aaa_user, t_platform_user, t_user;

Older InnoDB versions may report that OPTIMIZE is unsupported. In that case, rebuild the table with: ALTER TABLE table_name ENGINE='InnoDB'; This creates a new copy of the table, copies the data, then drops the old table. It is safe but you should back up first.

You can also start MySQL with options that enable OPTIMIZE for InnoDB:

service mysql start --skip-new

Engine‑specific behavior

MyISAM : OPTIMIZE TABLE performs three actions:

Repair the table if rows have been deleted or fragmented.

Analyze index pages.

Update table statistics if they are stale.

BDB : OPTIMIZE TABLE is currently mapped to ANALYZE TABLE.

InnoDB : OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table, updates index statistics, and frees unused space in the clustered index.

Best practices and caveats

Do not run OPTIMIZE too frequently; weekly or monthly is sufficient for most workloads.

Only tables that store variable‑length text types typically need defragmentation.

The operation locks the table while it runs, so schedule it during low‑traffic periods.

If you see the message “Table does not support optimize, doing recreate + analyze instead”, restart MySQL with --skip-new or --safe-mode to enable the operation.