In this tutorial, we will go over how to optimize MySQL InnoDB tables. The first step is to determine if your database is fragmented, and then connect to the MySQL database and run the following query mentioned below, which tells you how much free space each table has.

Log into MySQL command prompt:

mysql [(none)] > use racknerd_test;

mysql [racknerd_test]> select table_name, data_length, data_free
-> from information_schema.tables
-> where table_schema='<schema name>’
-> order by data_free desc;

To display data in MB, use the command mentioned below:

mysql [racknerd_test]> select table_name,
-> round(data_length/1024/1024) as data_length_mb,
-> round(data_free/1024/1024) as data_free_mb
-> from information_schema.tables
-> where round(data_free/1024/1024) > 450
-> order by data_free_mb;

This will display a list of all tables with at least 450MB of free space. As we can see, there are two tables in this example with more than 450MB of unused space. The “form” table is around 2.3GB in size. The “form” table has approximately 1.2GB of free space, and from these values, we can see that the tables are fragmented in a huge way, and to avoid it, we must optimize it.

We need to defrag using “optimize table” command:

mysql [(none)] > use racknerd_test;
mysql [racknerd_test]> OPTIMIZE TABLE form;

After optimization:

You can see the improvement after optimizing the table “form”.