How to Check and Optimize MySQL Table Space Fragmentation

MySQL tables can become fragmented after many DELETE, UPDATE, and INSERT operations, which reduces performance; regular table‑space maintenance can reclaim space and boost access speed.

Check Table Space Fragmentation

The experiment starts by inspecting a 1‑million‑row MyISAM table using ANALYZE TABLE and SHOW TABLE STATUS , confirming that Data_free is zero and file sizes match the OS view.

mysql> analyze table sbtest1;
+----------------+---------+----------+-----------------------------+
| Table          | Op      | Msg_type | Msg_text                    |
+----------------+---------+----------+-----------------------------+
| sbtest.sbtest1 | analyze | status   | Table is already up to date |
+----------------+---------+----------+-----------------------------+
1 row in set (0.06 sec)

mysql> show table status like 'sbtest1'\G
*************************** 1. row ***************************
           Name: sbtest1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1000000
 Avg_row_length: 729
    Data_length: 729000000
Max_data_length: 205195258022068223
   Index_length: 20457472
      Data_free: 0
 Auto_increment: 1000001
    Create_time: 2021-05-31 18:54:22
    Update_time: 2021-05-31 18:54:43
     Check_time: 2021-05-31 18:55:05
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

After deleting two‑thirds of the rows, the Data_free field shows about 66 % of the original space is now free, but the physical file size remains unchanged because deleted rows are only marked.

mysql> delete from sbtest1 where id%3<>0;
Query OK, 666667 rows affected (51.72 sec)

Running a full‑table scan before and after cleanup demonstrates the performance gain.

mysql> select count(*) from sbtest1 where c<>'aaa';
+----------+
| count(*) |
+----------+
|   333333 |
+----------+
1 row in set (0.82 sec)

Table Space Optimization and Performance Improvement

Reorganize the table with ALTER TABLE ... FORCE , which works like OPTIMIZE TABLE for MyISAM, InnoDB, and ARCHIVE engines.

mysql> alter table sbtest1 force;
Query OK, 333333 rows affected (10.73 sec)
Records: 333333  Duplicates: 0  Warnings: 0

mysql> analyze table sbtest1;
+----------------+---------+----------+-----------------------------+
| Table          | Op      | Msg_type | Msg_text                    |
+----------------+---------+----------+-----------------------------+
| sbtest.sbtest1 | analyze | status   | Table is already up to date |
+----------------+---------+----------+-----------------------------+
1 row in set (0.04 sec)

mysql> show table status like 'sbtest1'\G
... (Data_length reduced to ~243 MB, Data_free = 0) ...

After optimization, the on‑disk size drops to one‑third and the same query now runs in about 0.29 seconds, roughly three times faster.

For accurate benchmarking on Linux, clear the OS cache before each run:

# echo 3 > /proc/sys/vm/drop_caches

InnoDB does not support OPTIMIZE TABLE directly; use ALTER TABLE ... ENGINE=InnoDB instead.

mysql> OPTIMIZE TABLE sbtest2;
+----------------+----------+----------+---------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                          |
+----------------+----------+----------+---------------------------------------------------+
| sbtest.sbtest2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest2 | optimize | status   | OK                                                |
+----------------+----------+----------+---------------------------------------------------+

mysql> alter table sbtest2 engine=innodb;
Query OK, 0 rows affected (1 min 3.06 sec)

Use mysqlcheck for Batch Table Space Optimization

Identify tables with more than 10 MB of free space:

select table_name, round(data_length/1024/1024) as data_length_mb,
       round(data_free/1024/1024) as data_free_mb
from information_schema.tables
where round(data_free/1024/1024) > 10
order by data_free_mb desc
limit 10;

Then run mysqlcheck -o on individual tables, whole databases, or all databases:

$ mysqlcheck -o sbtest sbtest1
$ mysqlcheck -o sbtest
$ mysqlcheck -o --all-databases