Understanding and Resolving MySQL Table Fragmentation

MySQL table fragmentation is a common issue in database operations that can severely affect performance; this article provides a comprehensive explanation of fragmentation, its detection, causes, impact, and remediation techniques.

Detection method: Run the command show table status from table_name\G; and check the Data_free field—if it is non‑zero, fragmentation exists.

Causes:

Frequent DELETE operations leave empty spaces that new inserts may not fully reuse, gradually creating fragmentation.

Updating variable‑length columns (e.g., VARCHAR ) from longer to shorter values can also leave unused gaps.

Demonstration of DELETE impact: After inserting data into a table and performing a DELETE with a WHERE clause or LIMIT , the Data_free value increases, indicating fragmentation.

Demonstration of UPDATE impact: Create a table and insert a long string, then update it to a short string:

CREATE TABLE `t1` ( `k` varchar(3000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Update statement: update t1 set k='aaa';

Before update: length 223, Data_free 0. After update: length 3, Data_free 204 – fragmentation is evident.

Impact of fragmentation:

Non‑contiguous free space cannot be fully utilized, wasting storage.

Fragmented space causes random disk I/O, increasing the I/O load and slowing queries.

Cleanup methods:

MyISAM: OPTIMIZE TABLE table_name; (reorganizes data files and indexes).

InnoDB: Rebuild the table engine: ALTER TABLE table_name ENGINE=InnoDB; Export and re‑import the data.

Performance comparison after cleanup:

Database

Size Before Cleanup

Size After Cleanup

facebook

2.2G

1.1G

instagram

40G

22G

linkedin

555M

208M

twitter

107G

44G

SQL execution speed also improved. Example query:

select count(*) from test.twitter_11;

Before cleanup: 1 row in set (7.37 sec). After cleanup: 1 row in set (1.28 sec).

Conclusion: The comparison shows that cleaning up fragmentation saves considerable storage space and speeds up SQL execution; therefore, regular fragmentation cleanup should be part of routine database maintenance to ensure stable performance.