What is the use of OPTIMIZE TABLE statement in maintaining the MySQL tables?

While working with the database, we have a tendency to do plenty of changes like insert, update and delete data within the table which will cause the physical storage of the table fragment. As a result, the performance of database server is degraded.

MySQL provides us with OPTIMIZE TABLE statement that allows you to optimize the table to avoid this defragmenting problem. Its syntax would be as follows −

OPTIMIZE TABLE table_name

Here, table_name is the name of the table.

Example

We are running this statement for the table Student_info as follows −

mysql> Optimize table student_info\G
*************************** 1. row ***************************
   Table: query.student_info
      Op: optimize
Msg_type: status
Msg_text: OK
1 row in set (1.34 sec)