TheCodeBuzz

Today in this article, we will see how to MySQL OPTIMIZE table using CLI or Shell commands.

We will see how to optimize one or multiple tables or all the tables from a given database.

Even though MySQL is the most well-liked relational database management system, it occasionally needs to be optimized.

Additionally, regular optimization operations are crucial for proper system performance when dealing with large and complicated data sets.

A standard approach to improving MySQL performance involves modifying and optimizing the queries and measuring, and monitoring performance at multiple levels.

Today in this article, we will mainly focus on how to use the OPTIMIZE query in MySQL.

OPTIMIZE query helps but is not the replacement for everything, there are many best practices that you need to stick to get better results alongside to make your application trustworthy, speedy, and stable.

Optimization using OPTIMIZE of the database can be done to achieve the below,

• Helps in reorganizing the physical storage of table data and also associated index data

• Helps reduce storage space.

• Enhance I/O efficiency when accessing the table.

Below is an example of the overhead of memory on MySQL DB,

As a DBA (Database administrator) developer or tester, you may want to fine-tune the database at regular intervals to get better results.

Getting a list of tables in a database is a most useful query that most developers use.

Command- MySQL OPTIMIZE Table using MySQL

Let us validate the with the most basic command as below. The following query will show all tables in a MySQL database:

SHOW TABLES;

Once you know the table to be optimized, use the below query to optimize the same

OPTIMIZE TABLE table

MySQL OPTIMIZE All Table – Multiple tables

If you have multiple tables to be optimized, you can specify the table list as below,

OPTIMIZE TABLE table1, table2, table3

MySQL OPTIMIZE Table – Using the Linux Terminal

If you are using a Linux terminal then please use the below commands to optimize the tables

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

Optimization for multiple tables as below,

sudo mysqlcheck -o <schema> <table1> <table2> <table3> -u <username> -p <password> 

OPTIMIZE TABLE Output

OPTIMIZE TABLE the command returns a response as below with the columns shown in the following table.

Column	Value
Table	The table name
Op	Always optimize
Msg_type	status, error, info, note, or warning
Msg_text	An informational message

Example

Additional guidelines for MySQL OPTIMIZE

Please see below additional guidelines while using OPTIMIZE query in MySQL database,

• By Using OPTIMIZE TABLE table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.

• Helps in re-organizing the physical storage of table data and also associated index data.

• Enhance I/O efficiency when accessing the table.

• Use query when performing significant insert, update, or delete operations on a InnoDB table.

• OPTIMIZE TABLE works for
  • InnoDB, 
  • MyISAM, and 
  • ARCHIVE tables.

• OPTIMIZE TABLE works for partitioned tables.

• As default behavior, OPTIMIZE TABLE does not work for tables created using another storage engine.

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

---

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

---