Are you struggling with MySQL performance degradation? When the database size grows, query execution slows down, potentially affecting the performance of the entire application. An effective solution for such situations is the 「OPTIMIZE TABLE」 command. In this article, we’ll thoroughly explain MySQL’s 「OPTIMIZE TABLE」, covering everything from basic usage to best practices. We provide content useful for both beginners and intermediate users, helping you manage databases efficiently.
2. What is OPTIMIZE TABLE? An easy-to-understand explanation for beginners
Basic concept of OPTIMIZE TABLE
“OPTIMIZE TABLE” is a command for optimizing MySQL tables. This command is used for the following purposes:
Reclaim storage space: Retrieves unused space left after data is deleted.
Rebuild indexes: Organizes indexes to improve data access speed.
Update statistics: Refreshes statistical information to optimize query execution plans.
Simple explanations of terms
Storage engine: Defines how MySQL manages tables (e.g., InnoDB, MyISAM).
Defrag: A process that eliminates file fragmentation to improve storage efficiency.
Basic usage example
Below is the basic SQL command to execute “OPTIMIZE TABLE”:
OPTIMIZE TABLE table_name;
For example, to optimize a table named “users”, execute as follows:
OPTIMIZE TABLE users;
Overview of effects
Running “OPTIMIZE TABLE” can shrink table size and improve query speed. This is especially effective for tables that are frequently updated or deleted.
3. Best Practices for Running OPTIMIZE TABLE
Preparation Before Execution
Before running “OPTIMIZE TABLE”, it is recommended to perform the following preparations:
Obtain a backup
To prevent data loss in case of an emergency, please back up the table or the entire database.
Below is a simple backup example: mysqldump -u username -p database_name > backup.sql
Verify the storage engine
Ensure that you are using a storage engine that supports “OPTIMIZE TABLE”.
Example execution: SHOW TABLE STATUS WHERE Name = 'table_name';
Things to Watch Out for During Execution
Table lock
Since the table is locked during execution, it may affect the execution of other queries.
It is recommended to avoid busy times and run it during late night or maintenance windows.
Execution time
If the table size is large, optimization may take a long time.
In such cases, consider running it in parts or performing partial optimizations.
Post-Execution Verification
After running “OPTIMIZE TABLE”, an example command to verify the effect:
SHOW TABLE STATUS WHERE Name = 'users';
From this result, you can see changes in data size and index size.
4. Comparison of Alternative Methods and OPTIMIZE TABLE
Introduction to Alternative Methods
The following methods can be used instead of OPTIMIZE TABLE.
Manual optimization using ALTER TABLE … ENGINE=InnoDB
Data export & import using mysqldump
Utilizing partitioning
Table archiving and recreation
Manual optimization using ALTER TABLE … ENGINE=InnoDB
As an alternative to OPTIMIZE TABLE, manually executing ALTER TABLE allows for finer control.
How to Execute
ALTER TABLE table_name ENGINE=InnoDB;
For example, to optimize the users table:
ALTER TABLE users ENGINE=InnoDB;
Advantages
Provides almost the same effect as “OPTIMIZE TABLE.”
In some MySQL versions, it can be executed more safely than OPTIMIZE TABLE.
Disadvantages
If the table size is very large, downtime may occur.
Data Export & Import Using mysqldump
By using mysqldump to export data once and then importing it, you can refresh the entire database.
A table lock occurs during OPTIMIZE TABLE execution, causing a timeout.
Solution
Run during low-load periods
Increase the timeout value
SET innodb_lock_wait_timeout = 100;
“Out of Disk Space” Error
Error Details
ERROR 1030 (HY000): Got error 28 from storage engine
Cause
Insufficient disk space to create temporary files during OPTIMIZE TABLE execution.
Solution
Check available disk space
df -h
Change the temporary directory Edit my.cnf:
[mysqld]
tmpdir = /path/to/larger/tmp
Summary
In this section, we introduced common errors of “OPTIMIZE TABLE” and their solutions. When an error occurs, be sure to check the storage engine, address lock issues, and ensure sufficient disk space.
6. FAQ (Frequently Asked Questions)
Can running OPTIMIZE TABLE cause data loss?
Answer
Normally, running OPTIMIZE TABLE does not cause data loss, but if an error occurs during processing, the data could become corrupted. Therefore, it is recommended to take a backup beforehand.
It depends on how frequently data is deleted, but generally once a week to once a month is recommended. In the following cases, running it more often can be effective:
Tables with high data deletion
Fragmented indexes
Degraded query performance
Is it possible to automate OPTIMIZE TABLE?
Answer
Automation is possible by using MySQL’s event scheduler or cron jobs.
Using MySQL Event Scheduler
CREATE EVENT optimize_tables
ON SCHEDULE EVERY 7 DAY
DO
OPTIMIZE TABLE table_name;
Using cron jobs
crontab -e
Add the following line (runs every Sunday at 3 AM):
What to do if OPTIMIZE TABLE doesn’t have an effect?
Answer
Check the storage engine
SHOW TABLE STATUS WHERE Name = 'table_name';
Check the query execution plan
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
Update statistics
ANALYZE TABLE table_name;
If the table size is too large
mysqldump to take a backup and re-import
Consider partitioning
This FAQ introduced common questions about OPTIMIZE TABLE and their solutions.
7. Summary
In this article, we explained MySQL’s “OPTIMIZE TABLE” in detail.
Table optimization is essential for improving database performance, but its effectiveness can be limited if used in inappropriate situations.
In some cases, methods other than OPTIMIZE TABLE may be more appropriate.
Method
Advantages
Disadvantages
Applicable Scenarios
OPTIMIZE TABLE
Easy to execute
Causes table locks
Small to medium-sized tables
ALTER TABLE ENGINE=InnoDB
Similar optimization effect
Takes longer on large tables
InnoDB on MySQL 5.7 and later
mysqldump + Restore
Complete optimization of the entire table
Results in downtime
Optimizing large datasets
Final Checklist
✅ Are you using the appropriate storage engine? ✅ Did you take a backup? ✅ Will you run it during low-load periods? ✅ Did you consider whether an alternative method is needed?
Conclusion
To maintain MySQL performance, make proper use of “OPTIMIZE TABLE”!
We hope this article serves as a helpful reference for database management.
