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;

table_name data_length data_free
form

renewal

2310122

1500231

1134112

619012

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;

table_name data_length_mb data_free_mb
form

renewal

2310

1500

1134

619

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;

Table Op Msg_type Msg_tex
racknerd_test.form optimize status OK

After optimization:

table_name data_length_mb data_free_mb
form

renewal

1833

1500

0

619

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

Server Hosting Solutions by RackNerd:

Shared Hosting
cPanel Web Hosting in US, Europe, and Asia datacenters
Logo
Reseller Hosting
Create your new income stream today with a reseller account
Logo
VPS (Virtual Private Server)
Fast and Affordable VPS services - Instantly Deployed
Logo
Dedicated Servers
Bare-metal servers, ideal for the performance-demanding use case.
Logo

Leave a comment

Your email address will not be published.