Summary: in this tutorial, you will learn how to use the MySQL OPTIMIZE TABLE
statement to improve the performance of the database.
Introduction to MySQL OPTIMIZE TABLE statement
The OPTIMIZE
TABLE
statement allows you to reorganize the physical storage of table data to reclaim unused storage space and improve performance when accessing the table.
In practice, you’ll find the OPTIMIZE TABLE
statement useful in the following cases:
- Frequent deletions/updates: If a table has frequent updates or deletions, its data may be fragmented. The
OPTIMIZE TABLE
statement can help rearrange the storage structure and eliminate wasted space. - Table with variable-length rows: Tables with variable-length data such as
VARCHAR
,TEXT
, andBLOB
may become fragmented over time. By using theOPTIMIZE TABLE
statement, you can reduce the storage overhead. - Significant data growth and shrinkage: If your database experiences significant growth & shrinkage, you can run the
OPTIMIZE TABLE
periodically to maintain optimal storage efficiency.
Overall, using the OPTIMIZE TABLE
statement helps you optimize the storage space of table data and improve the query performance.
The OPTIMIZE TABLE
statement works with InnoDB, MyISAM, and ARCHIVE tables.
MySQL OPTIMIZE TABLE statement examples
Let’s take some examples of using the MySQL OPTIMIZE TABLE
statement.
1) Using the MySQL OPTIMIZE TABLE statement for MyISAM tables
For MyISAM tables, the OPTIMIZE TABLE
statement works as follows:
- Repair the table if it has deleted or split rows.
- Sort the index pages if they are not sorted.
- Update the table statistics if they are not up to date.
The following example illustrates the steps for optimizing a MyISAM table:
First, check the table status using the show table status
statement:
SHOW TABLE STATUS LIKE '<table_name>'\G
Code language: SQL (Structured Query Language) (sql)
It’ll return the output like this:
*************************** 1. row ***************************
Name: <table_name>
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 5000
Avg_row_length: 44
Data_length: 440000
Max_data_length: 281474976710655
Index_length: 105472
Data_free: 220000
Auto_increment: 10001
Create_time: 2023-11-21 07:34:39
Update_time: 2023-11-21 07:38:43
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
There are two important columns in the output regarding optimizing the table:
- The
Data_length
represents the space used by all rows in the table including any overhead as row headers. - The
Data_free
is the amount of free space (in bytes) in the data file. It indicates how much space can potentially be reclaimed by theOPTIMIZE TABLE
statement.
Second, optimize the table using the OPTIMIZE TABLE
statement:
OPTIMIZE TABLE <table_name>;
Code language: SQL (Structured Query Language) (sql)
Output:
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.text_data | optimize | status | OK |
+----------------+----------+----------+----------+
1 row in set (0.05 sec)
Code language: SQL (Structured Query Language) (sql)
The Msg_text
is OK
which indicates the optimization is successful.
Third, check the status of the table again:
SHOW TABLE STATUS LIKE '<table_name>'\G
Code language: SQL (Structured Query Language) (sql)
If the table space is fragmented, you’ll see Data_free
is zero:
*************************** 1. row ***************************
Name: <table_name>
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 5000
Avg_row_length: 44
Data_length: 440000
Max_data_length: 281474976710655
Index_length: 105472
Data_free: 0
Auto_increment: 10001
Create_time: 2023-11-21 08:03:12
Update_time: 2023-11-21 08:03:41
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
2) Using the MySQL OPTIMIZE TABLE statement for InnoDB tables
When you run the OPTIMIZE TABLE
statement on InnoDB
tables, you’ll get the following output:
+--------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status | OK |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.05 sec)
Code language: SQL (Structured Query Language) (sql)
The first message:
Table does not support optimize, doing recreate + analyze instead
Code language: SQL (Structured Query Language) (sql)
It means that the OPTIMIZE
TABLE
does not optimize the InnoDB
tables in the same way it optimizes the MyISAM tables. Instead, the OPTIMIZE
TABLE
statement performs the following actions:
- First, create a new empty table.
- Second, copy all rows from the original table into the new table.
- Third, delete the original table and rename the new tables.
- Finally, run the
ANALYZE
statement to gather table statistics.
One caution is that you should avoid running the OPTIMIZE
TABLE
statement on a large InnoDB
table when the disk space is low, as it is likely to cause the server to run out of space while attempting to recreate the large table.
Summary
- Use the
OPTIMIZE
TABLE
statement to reorganize the physical storage of tables to reduce disk space usage and improve query execution time.