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:
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:
The “LINUXHINT” is the name of a database to be selected.
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:
id INT AUTO_INCREMENT PRIMARY KEY,
In the above example, the table named “lh_table1” is being created.
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:
In the above example, the “OPTIMIZE TABLE” is being used with the table name “lh_table1”.
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:
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:
The output depicted that the multiple tables have been optimized.
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.