MySQL OPTIMIZE

In SQL language, the OPTIMIZE command reorganizes the physical storage of data and rebuilds the indexes. This command can therefore improve performance in some cases.
 

Syntax:

The OPTIMIZE command is executed in an SQL query using the following syntax:

OPTIMIZE TABLE table_name;

Of course, the keyword “table_name” must be replaced by the name of the table to be optimized.
 

 

Example :

Let’s imagine a system with a table that records the location of users. For some reason, an administrator will delete several thousand records. This deletion is done using the following query:

DELETE FROM location_log
WHERE id < 6000;

The table contained almost 12.8 MB of data before the data was deleted. Once the query is executed, the system still considers that there are 12.8 MB of data, because the data has not been reorganized. To optimize performance, the OPTIMIZE command is used:

OPTIMIZE TABLE location_log;

Once this query is executed, the system now displays a size of 6.5 MB, which reduces the weight of the database. Moreover, since the indexes are rebuilt, it improves the time of reading the data.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More