MySQL OPTIMIZE TABLE Statement

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, and BLOB may become fragmented over time. By using the OPTIMIZE 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>'\GCode 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 the OPTIMIZE 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>'\GCode 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 insteadCode 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.