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

on Database Management Systems, MySQL • January 21st, 2022 • Write for Hevo

Optimize MySQL Tables FI

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.

Table of Contents

What is MySQL?

Optimize MySQL Tables: MySQL Logo
Image Source

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. 
  • A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
  • Open-Source: MySQL is also a Free and Open-Source Relational Database Management System (RDBMS).
  • Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, Zendesk to name a few.

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.  

Simplify MySQL ETL with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo helps you integrate data from 100+ data sources (including 40+ Free Data Sources) like MySQL to a destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources provides users with the flexibility to bring in data of different kinds, in a smooth fashion without having to code a single line. 

GET STARTED WITH HEVO FOR FREE

Check Out Some of the Cool Features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
  • Connectors: Hevo supports 100+ Integrations from sources like MySQL to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Amazon Redshift, Firebolt, Snowflake Data Warehouses; Databricks, Amazon S3 Data Lakes, MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.  
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources, that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.

Simplify your Data Analysis with Hevo today! 

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

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
Image Source

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
Image Source

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
Image Source

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
Image Source

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.

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 100+ 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.

VISIT OUR WEBSITE TO EXPLORE HEVO

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.

No-code Data Pipeline For MySQL