Check Fragmented SQL Table
Slow queries and increasing file size after DELETE
Symptoms of a fragmented MyISAM table:
•Table gets bigger even though rows were deleted
•Index lookups get slower over time
•Random slow SELECTs
•High disk I/O for no reason
To detect whether a MyISAM table is fragmented, you can check several indicators.
Here are the easiest and most reliable methods:
Step 1:
SHOW TABLE STATUS LIKE 'your_table';
Look for these two columns:
Data_free
•Shows unused space (in bytes) inside the .MYD file
•Large value = lots of fragmentation
Data_length
•Size of actual data stored
Rule of thumb:
Data_free > 20–30% of Data_length
The table is fragmented and should be optimized.
Step 2:
Run MySQL’s internal check for MyISAM:
CHECK TABLE your_table;
You’ll see results like:
•status: OK → table is fine
•status: Table is fragmented → needs OPTIMIZE
•status: Table is not optimized → needs OPTIMIZE
This directly tells you whether fragmentation is present.
Topic last reviewed: 26/11/25