How to Optimize MySQL Tables

Introduction

Optimizing MySQL tables helps reorder information in a dedicated storage server to improve data input and output speeds. However, knowing when to use each optimization function and how to apply them to your situation is key to viable table maintenance.

This article provides practical tips and functions for MySQL table optimization.

Prerequisites

• MySQL version 8+ installed and configured (we are using 8.4).
• Access to the command prompt or command line.
• Fragmented database tables.

Why Should You Optimize MySQL Tables?

Unoptimized MySQL tables result from frequent update and delete queries, leading to fragmentation. The consequences are:

• The database table takes up more space on disk than it requires.
• Querying data takes more time than it should.

MySQL table optimization rearranges data inside a database table. It helps free up unused space and improve query speeds.

When Should You Optimize MySQL Tables?

Tables where information in a database continually updates, such as transactional databases, are the most likely candidates for optimization. However, optimizing large tables takes longer, and it locks tables. Locking a table for several hours may disrupt system operations.

For InnoDB engine tables, consider trying one of the following:

• Temporarily drop indexes. If a table and queries can function without indexes, a viable option is to drop the indexes, optimize, and add back the indexes. In some cases, this approach is faster than immediately optimizing.
• Focus on the primary index. Analyze which values benefit from defragmentation. Choosing the wrong index can lead to more fragmentation.

Find Tables for Optimization

There are multiple ways to find tables and analyze them for optimization. Start by connecting to your MySQL database:

USE [database_name];

Depending on your use case, try the queries below to see how to find optimization candidates.

Tip 1: Show Unused Space in Table

Check the status of a desired table with:

SHOW TABLE STATUS LIKE "[table_name]" \G

The output shows general information about the provided table. The following two metrics (in bytes) are essential:

• Data_length. Represents the total space used by the table.
• Data_free. Shows the unused space allocated to the table.

Tip 2: Show Unused Space for All Tables

The information schema stores metadata about a database schema. To check the allocated unused data for all tables in a schema, run:

SELECT table_name, data_length, data_free
FROM information_schema.tables
WHERE table_schema='[schema_name]'
ORDER BY data_free DESC;

The query displays the name of the table, the total space, and unused allocated space. By default, the values print in bytes.

Tip 3: Display Data in Megabytes

To print the data in megabytes, use:

SELECT table_name, 
ROUND(data_length/1024/1024) as DATA_LENGTH_MB,
ROUND(data_free/1024/1024) as DATA_FREE_MB
FROM information_schema.tables
WHERE table_schema='[schema_name]'
ORDER BY data_free DESC;

The example tables are not heavily fragmented. However, using optimize table helps free up some space from the test_table.

Optimize MySQL Tables via Command Line

There are multiple ways to optimize tables by defragmentation. However, the general steps you perform are similar.

Tip 1: Optimize Tables via OPTIMIZE TABLE Command

MySQL provides a specific command for optimizing a table:

OPTIMIZE TABLE [table_name];

The output shows an informative status message about the actions and the results of the optimization in a table. The command does not reflect the changes immediately due to caching.

To clear any cached data about the table size and free space, flush all tables:

FLUSH TABLES;

Then, analyze the table to confirm that the optimization has been applied and to update the table statistics:

ANALYZE TABLE [table_name];

This command sequence ensures that the table is fully optimized and its statistics are up to date.

OPTIMIZE TABLE Command in Different Storage Engines

The OPTIMIZE TABLE command behavior depends on the storage engine used for the table:

• InnoDB. The command rebuilds the table to reclaim unused space. The operation creates a temporary table copy and replaces the original table with the optimized one. The process requires significant disk space temporarily for large tables.
• MyISAM. The command reorganizes data to eliminate fragmentation, sorts indexes, and updates the table's statistics. Optimization is faster and requires less disk space.
• Memory. OPTIMIZE TABLE has no effect on these tables because the data is stored in memory and there is no fragmentation.
• CSV. The command recognizes the storage engine, but there are no significant performance improvements.
• Archive. The command compresses the table into a smaller and compact format.

Tip 2: Optimize Multiple Tables at Once

To optimize multiple tables at once, use:

OPTIMIZE TABLE [table_name_1], [table_name_2];

The result shows the status of the optimization for each optimized table. Proceed to flush and analyze the tables as described in Tip 1.

Tip 3: Optimize Tables via mysqlcheck Command

Perform table optimization through the Linux terminal with:

sudo mysqlcheck -o [schema] [table] -u [username] -p [password]

For example, to perform the check on a schema named test_db and a table named test_table using the root user credentials, run:

sudo mysqlcheck -o test_db test_table -u root -p

Provide the password when prompted.

To perform the optimization on multiple tables, separate each table name with a space:

sudo mysqlcheck -o [schema] [table_name_1] [table_name_2] -u [username] -p

Optimize MySQL Tables via GUI

Another way to optimize tables in MySQL is using the GUI. Most database management GUI tools offer a similar method to optimize tables.

The steps below show how to do this via MySQL Workbench:

1. Start MySQL Workbench and connect to the database instance.

Provide the user's password when prompted.

2. In the left panel, navigate to the Schemas view.

3. Expand the desired database schema, then expand Tables.

4. Right-click any table name -> Table Maintenance.

5. Switch to the Tables tab.

6. Select the table name from the list and click Optimize Table in the right pane.

The action opens a pop-up and shows the optimization results.

After Optimization

The results of the optimization change the values of data_length and data_free of the optimized table.

Both values are lowered, indicating:

• The optimization freed the unused allocated memory.
• The overall memory of the database is lower because of the released space.

Conclusion

Optimizing MySQL tables requires careful consideration. This tutorial should help you with some practical guidance and tips on when to optimize MySQL tables.

For more SQL optimization tools, check out our review of the 13 Best SQL Query Optimization Tools.