MySQL: Reclaiming disk space after data deletion

This documentation is part of the Troubleshooting guide. You can view the complete guide here: MySQL common issues and solutions.

👋 Welcome to the Stackhero documentation!

Stackhero offers a fully managed MySQL cloud service that streamlines deployment and management for your production workloads:

• Unlimited connections and data transfers.
• Integrated phpMyAdmin web interface for simplified database management.
• Effortless updates with one-click upgrades.
• Consistent performance and enhanced security on a private, dedicated infrastructure.

With Stackhero, you have a reliable MySQL database ready within minutes. Reduce the time spent on installation and maintenance, and focus on developing your applications. Try MySQL cloud hosting on Stackhero today.

When data is deleted from a MySQL table, the disk space it occupied is not immediately reclaimed. To recover this space, you can execute the following command:

OPTIMIZE TABLE <table>;

This process creates a temporary table, transfers the data to it, and then replaces the original table. During the optimisation process, the table will be locked and temporarily unavailable. The duration of this lock and the overall time required depend on the size of the table. Since this operation is resource-intensive, it is advisable to schedule it during periods of low server activity to minimise any impact on performance and availability.