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.When you delete or update any row, the table will create space in memory and you can eliminate extra space by performing Table optimization.
Optimize Table statement eliminate the disk fragmentation caused when you perform delete and update operation and reduce the waste of space.To use Optimize Table statement to optimize table for table optimization in MySQL.The Optimize Table command can be useful for this with using the following syntax.
The basic syntax Optimize TABLE statement is as follows
1 2 3 | OPTIMIZE TABLE table_name[,table_name] |
The above statement is valid for both the type InnoDB and MyISAM storage engine as Optimize Table only for MyISAM, BDB and InnoDB storage engines only.Optimize Table statement can only optimize the table VARCHAR, BLOB or TEXT data type only.Optimize TABLE statement implementation process will add to the table read-only lock in the Table
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 the table and will be filled up those unused space when there is any INSERT operation happened in the table.
How to apply optimize table query
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 the overhead 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.
When Update and delete operation performed, Whatever disk space allocated to that row will not automatically be recovered and for that Optimize table concept will help to de-fragment out the memory. You might like to read about Timestamp in MySQL.In short, You can use Optimize TABLE to reuse the unused space and to defragment the data file.
If you find this post useful, don’t forget to share it on Facebook and Google plus. Also, subscribe to my Email newsletter for more such updates.Do you have any comments about this article or the questions? I am sure you will have some great suggestions!
