MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications among others. MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. This Server is availed as a separate program and handles all the database instructions, commands, and statements.

The blog talks about the Optimize MySQL Tables Statement in detail eliciting key aspects like importance, the right time to leverage Optimize MySQL Tables Statement, and much more. It also gives a brief introduction to the salient features and benefits of MySQL.

What is MySQL?

Optimize MySQL Tables: MySQL Logo

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a variety of use cases like web applications. 

Since it comes standard in the LAMP Stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, PHP; MySQL is the go-to choice for scalable web applications. Let’s talk about a few salient features that make MySQL such a great catch. Also, see how you can integrate from MySQL to PostgreSQL

Key Features of MySQL

  • Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
  • Long History: MySQL has been around for over 20 years since its first release in 1995.
  • Frequent Updates: MySQL is getting more robust with frequent updates alongside new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
  • MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 


  • Availability of Fragmented Database Tables.
  • Presence of MySQL Version 8.0 configured and installed.
  • Access to the Command-Line or Command Prompt.

Why should you Optimize MySQL Tables?

The primary reason behind an unoptimized MySQL Table is routinely performed delete and update queries. The execution of these queries leads to fragmentation which has a couple of repercussions as follows:

  • Querying data takes more time than ideally expected.
  • The database table ends up pocketing more space than is ideally expected.

By leveraging MySQL Optimization techniques you can manage the arrangement of data within the database. This results in clean storage without unused, dispensable space which helps improve the speed of query execution, which in turn, improves overall efficiency. 

When is the right time to Optimize MySQL Tables?

Optimization is a requirement for tables that undergo continuous updates, like transactional databases. However, the time it takes for the optimization query to finish executing would depend on the size of the database. This is why locking a table for a long period is not favorable for transactional systems.

Here are a few use cases when the MySQL Optimize Tables statement comes in handy:

  • After executing a considerable number of update, insert, and delete operations on an InnoDB table that possesses its .ibd file. You can leverage the OPTIMIZE TABLES statement here since the InnoDB table was created with the innodb_file_per_table option enabled. In this instance, the indexes and tables get rearranged, and disk space can be recuperated for use by the operating system.
  • You can leverage the OPTIMIZE TABLES statement after deleting a substantial part of an ARCHIVE or MyISAM table containing variable-length rows (these are tables that have VARBINARY, VARCHAR, TEXT, or BLOB columns). In this instance, the deleted rows can be seamlessly maintained in a linked list. Any ensuing INSERT operations can reuse old row locations. You can leverage the OPTIMIZE TABLE statement to recuperate the unused space and defragment the data file. Following various thorough changes to a table, the OPTIMIZE TABLE statement might also tweak the performance of statements, sometimes quite considerably.  

Locating the Tables for Optimize MySQL Tables Statement

Here are a few key points to keep in mind when trying to look for tables to use the Optimize Tables Statement on:

Finding Optimize MySQL Tables: Depicting Unused Space within a Table

First, you need to check the status of the designated table with the following code snippet:

show table status like "<table name>" G
Optimize MySQL Tables: Depicting Unused Space within a Table
Optimize MySQL Tables: Depicting Unused Space within a Table

This output depicts some general information regarding the table. Data_length and Data_free are especially important. Data_length talks about the amount of space occupied by the database as a whole. Data_free, on the other hand, depicts the unused bytes in the database table. This information holds the key to identifying which tables need optimization and the amount of space that will be released subsequently.

Finding Optimize MySQL Tables: Displaying Unused Space for all MySQL Optimize Tables

The information schema for your designated database stores the metadata regarding a database schema. If you want to check the assigned unused data for all tables within a selected schema, run the following code snippet:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;
Optimize MySQL Tables: Displaying Unused Space for all Tables
Optimize MySQL Tables: Displaying Unused Space for all Tables

The aforementioned query depicts the name of the table, the unused assigned space, and the total space. The memory gets printed in bytes by default. 

Finding Optimize MySQL Tables: Depicting Data in Megabytes

You can print the data in megabytes by following the code snippet mentioned below:

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;
Optimize MySQL Tables: Depicting Data in Megabytes
Optimize MySQL Tables: Depicting Data in Megabytes

The example tables used in this instance aren’t heavily fragmented, but you can leverage the OPTIMIZE TABLE statement to release some space from the test_table.

Understanding How to Leverage the Optimize MySQL Tables Statement

There are various ways you can go about optimizing tables by leveraging defragmentation. Although, the general steps deployed for all these ways are pretty much the same. First, you need to make a temporary copy of the table where the MySQL Optimization is taking place. Then, when the operation completes its execution, the function will substitute the original table with the optimized table. MySQL will then rename the optimized table after the original.

Here are a few methods you can follow in setting up MySQL Optimize Tables:

Optimize MySQL Tables Statement: Optimizing a Table using MySQL

To optimize a MySQL Table using the OPTIMIZE TABLE statement, follow the syntax as follows:

OPTIMIZE TABLE <table name>;
Optimize MySQL Tables: Optimizing Using Mysql
Optimize MySQL Tables: Optimizing Using Mysql

The output depicts an informative status message about the results and actions of the MySQL Optimization in a tabular format.

Optimize MySQL Tables Statement: Optimizing Multiple MySQL Tables at once

MySQL also allows you to optimize multiple tables in a single command as follows:


The result of the aforementioned query will depict the status of the optimization for every optimized table.

Optimize MySQL Tables Statement: Optimizing MySQL Tables by Leveraging the Terminal

You can even perform the MySQL Optimization of the desired tables from the Linux terminal. Here’s the syntax for the same:

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

The following instance will optimize the DEPARTMENT table where the schema is ‘thefirststuff’ by leveraging the aforementioned syntax for the user credentials of the root user:

# mysqlcheck -o thefirststuff DEPARTMENT -u root -pMySQLSecretPwd99
thefirststuff.DEPARTMENT  OK

If you wish to perform the optimization on multiple tables, all you have to do is separate each table name with a space based on the following syntax:

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password> 

Understanding the aftermath of the MySQL Optimization

The MySQL Optimization process changes the values of data_free and data_length of the optimized table. Since, both the values have been lowered, this indicates that the MySQL Optimization was successful in freeing up the unused assigned memory. The overall memory of the database is, therefore, lower due to the released space.

So, for instance, say you optimized three tables ‘SALESINFO’, ‘EMPLOYEES’, and ‘FINANCES’, then you can execute the following command to fetch the size of the tables:

-- Query tables we'd optimized

On running the aforementioned query, here’s the result you get:

| EMPLOYEES  | 3791          | 0               |
| SALESINFO  | 10012         | 0               |
| FINANCES   | 11005         | 0               |


This blog discusses the different salient aspects of the MySQL Optimize Tables statement. It also gives a brief introduction to the salient benefits and features of MySQL before diving into the nitty-gritty of this article.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from 150+ Data Sources including Databases or SaaS applications like MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Want to take Hevo for a spin?SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Content Marketing Manager, Hevo Data

Amit is a Content Marketing Manager at Hevo Data. He is passionate about writing for SaaS products and modern data platforms. His portfolio of more than 200 articles shows his extraordinary talent for crafting engaging content that clearly conveys the advantages and complexity of cutting-edge data technologies. Amit’s extensive knowledge of the SaaS market and modern data solutions enables him to write insightful and informative pieces that engage and educate audiences, making him a thought leader in the sector.

No-code Data Pipeline For MySQL