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