How to Optimize Tables and Databases in MySQL

1. When to and Why Optimize Tables in MySQL
2. Optimize Tables in MySQL
3. Use MySQL Shell in Windows/Ubuntu 20.04 OS to Optimize Single or Multiple Tables
4. Use Windows Terminal to Optimize Table
5. Use Ubuntu 20.04 Terminal to Optimize Table
6. Optimize All Tables in One MySQL Database
7. Optimize All Databases in MySQL

This tutorial is a thorough guide on how we can optimize tables and databases in MySQL. We will be using two operating systems, Windows and Linux (Ubuntu 20.04).

It also educates about the importance of optimization in MySQL.

When to and Why Optimize Tables in MySQL

There is a higher possibility that the data files in MySQL are fragmented if our application is doing many DELETE and UPDATE operations on a database. It results in unused space that can also affect the performance.

It is highly needed that we should defrag our MySQL tables on an ongoing basis and reclaim the unused space. This is where we need table optimization in MySQL, which supports reordering the data in a dedicated storage server which ultimately enhances the performance and speed of data input and output.

Now, how do we know which table should we optimize? We should optimize the tables where the information (data) is continuously updating; for instance, the transactional databases are the perfect candidates for table optimization.

However, optimization queries can consume more time depending on the size of the tables and database. Therefore, it is not good for a transaction system to lock a table for many hours.

Instead, we can try a few tricks in the INNODB engine table. Some of the tricks are listed below:

1. Sometimes, optimizing the incorrect value can result in fragmentation with secondary indexes, so it is important to analyze how to get more benefits from compacting a particular value. It means identification of the correct value for optimization is very important.
2. Another way is to drop the index, optimize the tables, and add the indexes back. This way is only applicable if the table can work without indexes for a short time.

Optimize Tables in MySQL

First, we should analyze the tables that we want to optimize. We must be connected to our database using the following command.

Example Code:

-- Syntax: Use your_database_name;
mysql> USE test;

Once connected with the desired database, use the following query to get the table’s status.

Example Code:

-- Syntax: SHOW TABLE STATUS LIKE "your_table_name" \G
mysql> SHOW TABLE STATUS LIKE "test_table" \G

OUTPUT:

We have two important properties to know whether we should optimize this table or not.

1. The Data_length speaks about how much space a database takes up.
2. The Data_free tells the allocated but unused bytes within a database table.

This information guides us in identifying which table needs optimization and what amount of space we will reclaim afterward.

We can get these two numbers (Data_length and Data_free) for all tables in a particular database by using the following query. Currently, we have only one table named test_table in the test database.

Example Code:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

OUTPUT:

The above query prints the table’s names, total space in bytes, and allocated unused space in bytes. If you are comfortable working in Megabytes, you can use the following query to get output in Megabytes.

Example Code:

mysql> SELECT TABLE_NAME,
    -> round(data_length/1024/1024) AS Data_Length_in_MBs,
    -> round(data_free/1024/1024) AS Data_Free_in_MBs
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

Although the given example table is not heavily fragmented, we can reclaim the space using the OPTIMIZE TABLE command.

We will learn to optimize single/multiple tables or databases using MySQL shell and terminal.

Use MySQL Shell in Windows/Ubuntu 20.04 OS to Optimize Single or Multiple Tables

Example Code:

mysql> OPTIMIZE TABLE test_table;

OUTPUT:

To optimize multiple tables, use the following query.

Example Code:

mysql> OPTIMIZE TABLE tableName1, tableName2;

Now, use the following command to confirm that the desired table is optimized.

Example Code:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

OUTPUT:

The same queries will work on MySQL shell in Linux Operating System (Ubuntu 20.04).

Use Windows Terminal to Optimize Table

Example Code:

-- Syntax mysqlcheck -o <schema> <table> -u <username> -p <password>
mysqlcheck -o test test_table -u root -p

Once we write the command given above and hit Enter, we will be asked to enter the MySQL root password. Just enter that.

We must be in the bin folder to execute the query (see the following example).

Example Code:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -o test test_table -u root -p
Enter password: *****

Use Ubuntu 20.04 Terminal to Optimize Table

If we are signed in as superuser using the sudo su command, then execute the query as given below. It will only ask for a MySQL root password.

Example Code:

mysqlcheck -o test test_table -u root -p

If we are not signed in as superuser, we execute the mysqlcheck command. Here, we will be asked to enter the System’s root password and the MySQL root password.

Example Code:

sudo mysqlcheck -o test test_table -u root -p

Optimize All Tables in One MySQL Database

Optimize All Tables Using Windows Command Line:

-- Syntax: mysqlcheck -o your_database_name -u username -pPassword
mysqlcheck -o test -u root -p

Optimize All Tables Using Ubuntu Terminal:

-- if you are signed in as a superuser
mysqlcheck -o test -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o test -u root -p

Optimize All Databases in MySQL

Optimize All Databases Using Windows Command Line:

-- Syntax: mysqlcheck -o --all-databases -u username -pPassword
mysqlcheck -o --all-databases -u root -p

Optimize All Databases Using Ubuntu Terminal:

-- if you are signed in as a superuser
mysqlcheck -o --all-databases -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o --all-databases -u root -p