Back to Top

How To Optimize Queries and Tables in MySQL

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.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

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.

To optimize Table

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

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

optimize mysql table

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

Problem with German special character

Posted on 6 years ago

Bhumi

How to use CSS3 Object-fit

Posted on 3 years ago

Bhumi

How to connect to MySQL database using PHP

Posted on 5 years ago

Bhumi