![workbench mysql optimizer workbench mysql optimizer](https://image.slidesharecdn.com/optimizeroow2017-171004201308/95/mysql-optimizer-whats-new-in-80-47-638.jpg)
WORKBENCH MYSQL OPTIMIZER ARCHIVE
![workbench mysql optimizer workbench mysql optimizer](https://image.slidesharecdn.com/optimizeroow2017-171004201308/95/mysql-optimizer-whats-new-in-80-29-1024.jpg)
You can also optimize multiple tables in a single command as shown below. The following example will optimize EMPLOYEE table. The first method is to use Optimize table command as shown below.
![workbench mysql optimizer workbench mysql optimizer](https://image.slidesharecdn.com/optimizeroow2017-171004201308/95/mysql-optimizer-whats-new-in-80-54-1024.jpg)
If we optimize this table, the size of this file should go down dramatically. In this example, the EMPLOYEE.MYD file is taking up around 22GB at the filesystem level, but it has lot of unused space in it. 1 mysql mysql 22G Apr 23 12:03 EMPLOYEE.MYD 1 mysql mysql 14G Apr 23 12:53 DEPARTMENT.MYD 1 mysql mysql 7.6G Apr 23 10:55 BENEFITS.MYD The file size will be the same as what you see under “data_length_mb” column in the above output. All these three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are heavily fragmented and it needs to be optimized to reclaim the unused space.įrom the filesystem level, you can see the size of the individual table files as shown below.For example, EMPLOYEE table has around 19GB of unused space in it. data_free_mb column displays the total unused space in that particular table.For example, EMPLOYEE table size is around 21GB. data_length_mb column displays the total table size in MB.As we see above, in this example, there are 3 tables that has more than 500MB of unused space. This will display list of all tables that has minimum of 500MB of unused space.| table_name | data_length_mb | data_free_mb | The first step is to identify whether you have fragmentation on your MySQL database.Ĭonnect to your MySQL database, and execute the following query, which will display how much unused space are available in every table.
WORKBENCH MYSQL OPTIMIZER HOW TO
This tutorial explains how to optimize MySQL to defrag tables and reclaim unused space. So, it is highly recommended that you defrag your MySQL tables on an ongoing basis. This will result in lot of unused space, and also might affect performance. If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented.