MySQL MariaDB

How to Use OPTIMIZE TABLE Statement in MySQL?

In MySQL, efficient table storage plays a crucial role in ensuring optimal performance and disk space efficiency. However, tables can become fragmented over time which badly affects the storage allocation and query execution speed. To address these kinds of problems, MySQL administrators can use the “OPTIMIZE TABLE” statement. It reclaims space, defragments data, organizes indexes, query processing speed, and improves database performance and disk space utilization.

This post will illustrate how to use the “OPTIMIZE TABLE” statement in MySQL using different examples.

How to Utilize OPTIMIZE TABLE Statement in MySQL?

The “OPTIMIZE TABLE” statement in MySQL is utilized to optimize the storage of a table. The “OPTIMIZE TABLE” statement can be used with the appropriate table name as shown in the given below syntax:

OPTIMIZE TABLE [table_name];

 

In the above syntax, the optimization will be applied to the “[table_name]”.

Let’s learn the use of the “OPTIMIZE TABLE” statement in MySQL using the following steps.

Step 1: Login and Select Database

To use the “OPTIMIZE TABLE” statement in MySQL, first, log in to the server and select any particular database by utilizing the “USE” keyword followed by the database name as shown in the below command:

USE LINUXHINT;

 

The “LINUXHINT” is the name of a database to be selected.

Output

The output demonstrated that the selected database has been changed/selected.

Step 2: Select or Create a Table

After selecting the database, you must have a table in the database. If there is no table available in your MySQL database, then you can create it by using the “CREATE TABLE” statement as provided below:

CREATE TABLE lh_table1 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(100)
);

 

In the above example, the table named “lh_table1” is being created.

Output

Step 3: Use OPTIMIZE TABLE Statement

The “OPTIMIZE TABLE” statement in MySQL can be utilized with a single table or multiple tables at the same time.

Use OPTIMIZE TABLE Statement With One Table

To use the statement for a single table, use the “OPTIMIZE TABLE” clause followed by a particular table name as given below in the example:

OPTIMIZE TABLE lh_table1;

 

In the above example, the “OPTIMIZE TABLE” is being used with the table name “lh_table1”.

Output

The output showed that the table has been optimized.

Use OPTIMIZE TABLE Statement With Multiple Tables

To use the “OPTIMIZE TABLE” statement with multiple tables, write the multiple tables’ names after the “OPTIMIZE TABLE” clause as shown in the below syntax:

OPTIMIZE TABLE [table1_name], [table2_name], [table3_name];

 

The “[table1_name]”, “[table2_name]” and “[table3_name]” are multiple tables to be optimized in the above syntax.

An example of using the “OPTIMIZE TABLE” statement with multiple tables, name “categories”, “linuxhint_employees” and “linuxhint_products” is given below:

OPTIMIZE TABLE categories, linuxhint_employees, linuxhint_products;

 

Output

The output depicted that the multiple tables have been optimized.

Conclusion

The “OPTIMIZE TABLE” statement in MySQL is used to enhance the storage efficiency of database tables which can be helpful in improving the query performance and reducing disk space usage. To optimize the storage of tables in MySQL, firstly, log in to the MySQL server, select the desired database, and create a table if needed. After that use the “OPTIMIZE TABLE” clause with single or multiple table names to optimize their storage as guided in the above post.

About the author

Danish Ghafoor

I am a computer science graduate with a certification in cybersecurity. My passion for technology has led me to develop a deep understanding of the challenges and solutions in the field of information security. I am dedicated to staying up-to-date with the latest industry trends and technologies, and constantly seeking out new opportunities to expand my knowledge and skills.