MySQL OPTIMIZE TABLE Statement

This tutorial explains MySQL OPTIMIZE TABLE statement which defrags tables and recovers unused space. We’ll describe the complete usage of this method with the help of simple examples.

If your database is receiving a lot of deletes and updates calls, then it might lead to fragmentation in your MySQL data files.

Therefore, a lot of unused space would go in vain, and also put a high impact on the performance.

So, experts recommend that you must defrag your MySQL tables regularly. Hence, we’ll today explain how to use the MySQL OPTIMIZE TABLE to defrag tables and free up space.

MySQL OPTIMIZE TABLE

Before you do optimization, first confirm whether your MySQL database is suffering from fragmentation or not. To know it, run the below command.

Check Tables for Optimization

You need to analyze which table is consuming more space in your database. Hence, connect to the MySQL DB instance, and run the below query.

It should fetch the tables which are accounting for the unused space.

-- List all tables causing unused space
SELECT TABLE_NAME,
       ROUND(DATA_LENGTH/1024/1024) AS USED_SPACE_MB, 
       ROUND(DATA_FREE/1024/1024) AS UNUSED_SPACE_MB 
FROM INFORMATION_SCHEMA.TABLES 
WHERE ROUND(DATA_FREE/1024/1024) > 1000 
ORDER BY UNUSED_SPACE_MB;

After running the above SQL query, you shall see this type of result:

+------------+---------------+-----------------+
| TABLE_NAME | USED_SPACE_MB | UNUSED_SPACE_MB |
+------------+---------------+-----------------+
| EMPLOYEES  | 6917          | 5284            |
| SALESINFO  | 21473         | 11097           |
| FINANCES   | 11825         | 21286           |
+------------+---------------+-----------------+

We can interpret the following facts from the output:

• First, the SELECT command is listing all tables that are causing more than 1000 MB of free space.
• The columns USED_SPACE_MB and UNUSED_SPACE_MB are showing data in MB.
• The results indicate that all three tables are a candidate for optimization as they are causing high fragmentation.

MySQL OPTIMIZE TABLE Command

This command uses the following syntax:

mysql> OPTIMIZE TABLE table1 [, table2 ] ...

We can use the above in one of the following ways:

First, we optimize one table using this MySQL statement.

mysql> OPTIMIZE TABLE EMPLOYEES;

Secondly, we can optimize multiple tables together, as shown below:

mysql> OPTIMIZE TABLE EMPLOYEES, SALESINFO, FINANCES;

While running optimization on a table, MySQL does the following tasks:

• Creates a temp table,
• Deletes the original one after optimizing it, and
• Rename the temp table to the original name in the end.

Post-MySQL Optimization

After finishing up with optimization, you can issue the below command. It will fetch the size of the total as well as the unused-space the three tables are claiming.

-- Query tables we'd optimized
SELECT TABLE_NAME,
       ROUND(DATA_LENGTH/1024/1024) AS USED_SPACE_MB, 
       ROUND(DATA_FREE/1024/1024) AS UNUSED_SPACE_MB 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME in  
('EMPLOYEES', 'SALESINFO', 'FINANCES');

After running the above SQL query, you shall see this type of result:

+------------+---------------+-----------------+
| TABLE_NAME | USED_SPACE_MB | UNUSED_SPACE_MB |
+------------+---------------+-----------------+
| EMPLOYEES  | 3791          | 0               |
| SALESINFO  | 10012         | 0               |
| FINANCES   | 11005         | 0               |
+------------+---------------+-----------------+

You can easily deduce from the outcome that MySQL OPTIMIZE TABLE command has significantly reduced the size. And, unused space is no more.

Also, the table sizes have come down too. And it helped us fix a lot of fragmentation at the filesystem level.

Anyways, we hope that after wrapping up this tutorial, you should feel comfortable in optimizing the tables. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.