How to Optimize MySQL Tables? | 2024’s Critical Step Guide

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?

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. 

Prerequisites

• 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:

• Depicting Unused Space within a Table
• Displaying Unused Space for all MySQL Optimize Tables
• Depicting Data in Megabytes

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

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;

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;

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:

• Optimizing a Table using MySQL
• Optimizing Multiple MySQL Tables at once 
• Optimizing MySQL Tables by Leveraging the Terminal

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>;

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:

mysql> OPTIMIZE TABLE EMPLOYEE, DEPARTMENT, BENEFITS

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
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');

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

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

Conclusion

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.