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.

How To Optimize MySQL Tables

Prerequisites

  • MySQL version 8.0 installed and configured
  • Access to the command prompt or command line
  • Fragmented database tables

Why Should You Optimize MySQL Tables?

The main reason for an unoptimized MySQL table is frequently performed update and delete queries. In turn, this causes fragmentation and there are some consequences:

1. The database table takes up more space than it needs.

2. Querying data takes more time than it should.

MySQL table optimization techniques address the arrangement of data inside a database. The result is clean storage without redundant, unused space, which helps speed up queries.

Note: Tuning is another technique for improving the query performance. Check out our article on How to Improve MySQL Performance with Tuning.

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, depending on the size of the database, the optimization query takes a long time to finish with larger tables. Therefore, locking a table for a long number of hours is not beneficial for transactional systems.

Instead of optimizing a table right away, consider trying some of these tricks for INNODB engine tables:

  • Drop index, optimize, and add back index. If a table works without indexes for some time, dropping, optimizing, and adding back the indexes performs faster in some cases.
  • Analyze which value benefits from compacting the most. Sometimes, the primary index is not useful or problematic. Optimizing for the wrong value causes fragmentation with secondary indexes.

Find Tables for Optimization

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

use <database name>

Depending on your use case, filter the information needed by following one of the tips below.

Tip 1: Show Unused Space in a Table

Check the status of the desired table with:

show table status like "<table name>" \G
Output of the show table status command in MySQL

The output shows some general information about the table. The following two numbers are important:

  • Data_length represents the amount of space the database takes up in total.
  • Data_free shows the allocated unused bytes within the database table. This information helps identify which tables need optimization and how much space will be released afterward.

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 selected schema, run:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;
Output of selecting information schema

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

Note: To display information for all databases, omit the line where table_schema='<schema name>'.

Tip 3: Display Data in Megabytes

Print the data in megabytes with:

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;
Output of selecting information schema in MB

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

Optimize Tables

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

First, the process makes a temporary copy of the table where the optimization occurs. Then, when the operation finishes, the function replaces the original table with the optimized, renaming it after the original.

Tip 1: Optimize a Table Using MySQL

MySQL provides a function for optimizing a table. To optimize a table, use:

OPTIMIZE TABLE <table name>;
Output of the command optimize table

The output shows an informative status message about the actions and the results of the optimization in a tabular format.

Tip 2: Optimize Multiple Tables at Once

To optimize multiple tables at once, use:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;
Output of the command optimize table for multiple tables

The result shows the status from the optimization for each optimized table.

Tip 3: Optimize Tables Using the Terminal

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 and table named test_table using the root user credentials, run:

sudo mysqlcheck -o test test_table -u root

Note: Getting an access denied error? Read our guide on how to handle the issue: How to Solve MySQL Error: Access denied for user root@localhost.

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

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

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:

1. The optimization freed the unused allocated memory.

2. The overall memory of the database is lower because of the released space.

Values before and after optimization of a MySQL table

Conclusion

Optimizing MySQL tables requires careful consideration. This tutorial should help provide 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.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is an aspiring technical writer at phoenixNAP and a data nerd. Her background in Electrical Engineering and Computing and her teaching experience give her a unique set of skills - being able to easily explain complex technical concepts through her content.
Next you should read
7 Best Website Speed and Performance Testing Tools
June 3, 2019

There are programs that answer two of the most common questions web administrators and SEO's keep asking when it comes to server response time: How fast...
Read more
What Is Data Replication?
June 17, 2021

Database replication is used to improve data security, increase availability and performance, and make the database more robust. This tutorial covers different methods for database replication.
Read more
MySQL Commands Cheat Sheet
January 20, 2021

Need a reference sheet for all the important MySQL commands? Check out this MySQL Commands article which includes a downloadable MySQL cheat sheet in PDF format.
Read more
How to Create or Add an Index in MySQL With Examples
August 31, 2020

An index is an integral part of MySQL, helping with organizing and searching information more easily. This guide explains how to add an index to new...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.