How to Detect and Remove MySQL Table Fragmentation for Faster Queries

Table storage can become fragmented when rows are deleted, leaving empty space that MySQL still scans, degrading query performance.

Viewing Fragmentation Size

Run SHOW TABLE STATUS LIKE 'table_name'; and check the Data_free column for the amount of free space (fragmentation).

Listing All Fragmented Tables

Execute the following query to list schemas with non‑zero data_free:

select table_schema db, table_name, data_free, engine
from information_schema.tables
where table_schema not in ('information_schema','mysql')
  and data_free > 0;

Removing Fragmentation

MyISAM tables : OPTIMIZE TABLE table_name; InnoDB tables :

ALTER TABLE table_name ENGINE=InnoDB;

Recommendations

Fragment removal locks the table temporarily; larger data takes longer. Automate the process with a script that runs during low‑traffic periods (e.g., early Wednesday mornings) and cleans tables whose Data_free exceeds a chosen threshold.