HOW TO OPTIMIZE QUERIES AND TABLES IN MYSQL

An optimized table structure is different from a created table. Table optimization is the process of retrieving an unused space after deletions of row and or cleaning up the table after any modification or deletion have been made in the structure of the table.

The OPTIMIZE TABLE command can be useful for this with using the following syntax. 

SYNTAX:

1 2 3

OPTIMIZE TABLE table_name[,table_name]

For Example, In PHPMyAdmin there is one table customer and Overhead of the table is 24KB see in below screen.

Overhead is the unused space in the table which was created when any row deleted from table and will be filled up those unused space when there is any INSERT operation happened in table.

So I have applied optimize table query

1 2 3

optimize table customers;

And I get below result shows that table was optimized successfully.Table optimization removes theoverhead from tables.

NOTE: Remember that tables are locked while table optimization performed so if your table is large so perform the optimization during scheduled downtime or when low traffic is flowing to your system.