JIYIK CN >

This tutorial is an exhaustive guide on how to optimize tables and databases in MySQL. We will be using two operating systems, Windows and Linux (Ubuntu 20.04).

It also introduces the importance of optimization in MySQL.

When and Why to Optimize Tables in MySQL

If our application performs many DELETEand UPDATEoperations on the database, there are higher chances of data files fragmentation in MySQL. It results in unused space which also affects the performance.

There is a great need to continuously defragment MySQL tables and reclaim unused space. This is where we need table optimization in MySQL, which enables reordering of data in dedicated storage servers, ultimately improving the performance and speed of data input and output.

Now, how do we know which table we should optimize? We should optimize tables where information (data) is constantly updated; for example, a transactional database is a perfect candidate for table optimization.

However, optimizing the query may consume more time, depending on the size of the table and database. Therefore, it is not good for the transaction system to lock the table for many hours.

Instead, we can INNODBtry a few tricks in the Engine table. Here are some of them:

Optimizing tables in MySQL

First, we should analyze the table we want to optimize. We have to connect to our database using the following command.

Sample code:

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

Once connected with the required database, use the following query to get the status of the table.

Sample 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 if we should optimize this table.

This information guides us in determining which tables need to be optimized and the amount of space we will reclaim afterwards.

Data_lengthWe can get these two numbers ( and ) for all tables in a particular database using the following query Data_free. Currently, we testhave only one test_tabletable named in the database.

Sample 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 name of the table, total space in bytes, and allocated unused space in bytes. If you are comfortable working in MB, you can use the following query to get the output in MB.

Sample 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 severely fragmented, we can OPTIMIZE TABLEreclaim space using the command.

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

Optimize single or multiple tables using MySQL Shell in Windows/Ubuntu 20.04 operating system

Sample code:

mysql> OPTIMIZE TABLE test_table;

Output:

To optimize multiple tables, use the following query.

Sample code:

mysql> OPTIMIZE TABLE tableName1, tableName2;

Now, confirm that the required tables are optimized using the following command.

Sample code:

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

Output:

Optimizing tables using Windows Terminal

Sample 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 Enter, we will be asked for the MySQL rootpassword. Enter that.

We must be binin the folder to perform the query (see the example below).

Sample code:

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

Optimize tables using Ubuntu 20.04 terminal

If we log in sudo suas mysql using command 超级用户and execute the query given below. It will only ask for MySQL rootpassword.

Sample code:

mysqlcheck -o test test_table -u root -p

If we are not 超级用户logged in as , we will execute mysqlcheckthe command. Here we will be asked for the system's rootand MySQL rootpasswords.

Sample code:

sudo mysqlcheck -o test test_table -u root -p

Optimize all tables in a MySQL database

To optimize all tables using the Windows command line:

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

Use the Ubuntu terminal to optimize all tables:

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

To optimize all databases using the Windows command line:

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

Use the Ubuntu terminal to optimize all databases:

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