How To Optimize MySQL Tables {Step-by-Step Guide} – phoenixNAP

Mysql optimize table innodb


MySQL table optimization helps reorder information on 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 optimizing MySQL tables.

<img src="" alt="How

to optimize MySQL tables” />



  • version 8.0 installed and configured
  • Command prompt or command-line access
  • Fragmented database tablesWhy

should I optimize MySQL tables?

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

1. The database table takes up more space than you need.

2. Querying data takes longer than it should.

MySQL table optimization techniques address the arrangement of data within a database. The result is clean storage with no redundant and unused space, helping to speed up queries.

When should you optimize MySQL tables?

Tables where information in a database is continuously updated, such as transactional databases, are the most likely candidates for optimization.

However, depending on the size of the database, it takes a long time for the optimization query to end up with larger tables. Therefore, blocking 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:

Delete index,

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


tables for optimization

There are several ways to display tables and analyze them for optimization. Start by connecting to

your MySQL database: use <database name>

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

Tip 1: Show unused space in a

table Check the status of the

desired table with: display table status

as “<table name>” G

<img src=”” alt=”Show table status command output in MySQL” />

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

  • Data_length represents the amount of space occupied by the database in total
  • .

  • Data_free displays the unused bytes allocated within the database table. This information helps identify which tables need optimization and how much space will be freed up afterwards.

Tip 2: Show unused space for all tables

The information schema

stores metadata about a database schema. To check the unused data mapped for all tables in a selected schema, run:

select table_name, data_length, data_free from information_schema.tables where table_schema='<schema name>’ sort by data_free desc;

Information schema selection result

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

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>’ sort by data_free desc;

Result of selecting the information schema in MB

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



There are several ways to optimize tables using 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 is finished, the function replaces the original table with the optimized one, renaming it after the original.

Tip 1: Optimize

a table using MySQL

MySQL provides a function to optimize a table. To optimize a table, use:

OPTIMIZE TABLE <table name>;

<img src=”” alt=”Optimize Table Command Output” /


The output displays an informational status message about optimization actions and results in tabular format.

Tip 2:


multiple tables at once

To optimize multiple tables at once, use:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

<img src=”” alt=”Edit Table Command Output for Multiple Tables” /


The output shows the optimization status for each optimized table.

Tip 3: Optimize tables using the


Perform table optimization via Linux terminal with:

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

For example, to perform the check on a schema named test and a table named test_table with root credentials , run

: sudo mysqlcheck -o test test_table -u root

To perform 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 optimization change the data_length and data_free values of the optimized table. Both values are reduced, indicating:

1. The optimization freed up unused allocated memory.

2. The total memory of the database is smaller due to the space freed.

Values before and after optimizing a MySQL table


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

Contact US