When managing MySQL databases, understanding how storage space is handled after data deletion is crucial. Many developers assume that deleting records automatically reclaims disk space, but this isn't the case. This article provides a practical demonstration showing exactly why OPTIMIZE TABLE is essential for maintaining efficient MySQL deployments.

Initial Dataset Overview

Record Count

mysql> SELECT COUNT(*) AS total FROM user_activity_log;
+---------+
| total   |
+---------+
| 1187096 |
+---------+
1 row in set (0.04 sec)

Physical File Sizes

[root@dbserver data]$ ls -lh user_activity_log.*
-rw-rw---- 1 mysql mysql 373M user_activity_log.MYD
-rw-rw---- 1 mysql mysql 124M user_activity_log.MYI
-rw-rw---- 1 mysql mysql  12K user_activity_log.frm

The table contains 1.18 million records, with the data file occupying 373MB and the index file taking 124MB on disk.

Index Structure Details

mysql> SHOW INDEX FROM user_activity_log FROM appdb;
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name            | Seq_in_index | Column_name   | Cardinality | Sub_part     | Packed  | Null   | Index_type |
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+
| user_activity_log|          0 | PRIMARY            |            1 | id            |     1187096 | NULL         | NULL    |        | BTREE       |
| user_activity_log|          1 | campaign_id_idx    |            1 | campaign_id   |          46 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | record_tracking     |            1 | tracking_key  |     1187096 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | source_page_idx     |            1 | source_page   |       30438 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | client_ip_idx       |            1 | client_ip     |      593548 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | server_port_idx     |            1 | server_port   |       65949 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | session_idx         |            1 | session_key   |     1187096 | NULL         | NULL    | YES    | BTREE       |
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+
7 rows in set (0.28 sec)

Index cardinality information helps understand data distribution across indexed columns. Higher cardinality values indicate better selectivity for query optimization.

Impact of Deleting Half the Records

mysql> DELETE FROM user_activity_log WHERE id > 598000;
Query OK, 589096 rows affected (4 min 28.06 sec)

File Sizes After Deletion

[root@dbserver data]$ ls -lh user_activity_log.*
-rw-rw---- 1 mysql mysql 373M user_activity_log.MYD
-rw-rw---- 1 mysql mysql 124M user_activity_log.MYI
-rw-rw---- 1 mysql mysql  12K user_activity_log.frm

This reveals a critical insight: deleting 589,000 records (nearly half the data) resulted in zero reduction in physical file sizes. The MYD and MYI files remain unchanged, demonstrating that MySQL merely marks deleted rows as available space without actually reclaiming disk capacity.

Index Cardinality After Deletion

mysql> SHOW INDEX FROM user_activity_log;
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name            | Seq_in_index | Column_name   | Cardinality | Sub_part     | Packed  | Null   | Index_type |
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+
| user_activity_log|          0 | PRIMARY            |            1 | id            |      598000 | NULL         | NULL    |        | BTREE       |
| user_activity_log|          1 | campaign_id_idx    |            1 | campaign_id   |          23 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | record_tracking     |            1 | tracking_key  |      598000 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | source_page_idx     |            1 | source_page   |       15333 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | client_ip_idx       |            1 | client_ip     |      299000 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | server_port_idx     |            1 | server_port   |       33222 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | session_idx         |            1 | session_key   |      598000 | NULL         | NULL    | YES    | BTREE       |
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+

The cardinality values appropriately halved after deletion, which is expected behavior for index statistics.

Running OPTIMIZE TABLE

mysql> OPTIMIZE TABLE user_activity_log;
+------------------------+----------+----------+----------+
| Table                  | Op       | Msg_type | Msg_text |
+------------------------+----------+----------+----------+
| appdb.user_activity_log | optimize | status   | OK       |
+------------------------+----------+----------+----------+
1 row in set (1 min 21.05 sec)

File Sizes After Optimization

[root@dbserver data]$ ls -lh user_activity_log.*
-rw-rw---- 1 mysql mysql 178M user_activity_log.MYD
-rw-rw---- 1 mysql mysql  64M user_activity_log.MYI
-rw-rw---- 1 mysql mysql  12K user_activity_log.frm

After optimization, the data file dropped from 373MB to 178MB (roughly 52% reduction), and the index file decreased from 124MB to 64MB (approximately 48% reduction). This confirms that OPTIMIZE TABLE effectively reclaims the space previously occupied by deleted records.

Updated Index Statistics

mysql> SHOW INDEX FROM user_activity_log;
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name            | Seq_in_index | Column_name   | Cardinality | Sub_part     | Packed  | Null   | Index_type |
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+
| user_activity_log|          0 | PRIMARY            |            1 | id            |      598000 | NULL         | NULL    |        | BTREE       |
| user_activity_log|          1 | campaign_id_idx    |            1 | campaign_id   |          42 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | record_tracking     |            1 | tracking_key  |      598000 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | source_page_idx     |            1 | source_page   |       24916 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | client_ip_idx       |            1 | client_ip     |      598000 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | server_port_idx     |            1 | server_port   |       59800 | NULL         | NULL    | YES    | BTREE       |
| user_activity_log|          1 | session_idx         |            1 | session_key   |      598000 | NULL         | NULL    | YES    | BTREE       |
+------------------+------------+---------------------+--------------+---------------+-------------+--------------+----------+--------+------+------------+---------+

Post-optimization cardinality shows significant improvements: campaign_id_idx increased from 23 to 42, source_page_idx jumped from 15,333 to 24,916, and server_port_idx nearly doubled from 33,222 to 59,800. These enhanced statistics enible the query optimizer to make more efficient execution plans.

Understanding the Mechanism

MySQL's storage engine maintains deleted rows in a linked list rather than immediately freeing the space. New INSERT operations gradually reuse these marked-for-deletion slots. For tables with heavy DELETE operations but infrequent insertions, this approach leads to significant wasted disk space and fragmented storage.

The practical implication is straightforward: tables experiencing frequent deletions should undergo periodic optimization to reclaim storage and defragment data files. A monthly optimization schedule is typically sufficient for most applications, though high-turnover tables may require more frequent maintenance.

OPTIMIZE TABLE Syntax and Behavior

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE is particularly valuable after bulk deletions from tables containing VARCHAR, BLOB, or TEXT columns, as these variable-length data types are more susceptible to fragmentation. The operation works with MyISAM, InnoDB, and BDB storage engines.

Important considerations:

• The operation acquires a table-level lock during execution
• Only run when necessary—weekly or monthly intervals suffice for most scenarios
• For InnoDB tables, the behavior may differ slightly depending on configuration
• Consider scheduling during low-traffic periods to minimize impact on concurrent operations