Steps to Optimize Tables in MySQL

Follow the below-given steps to optimize MySQL tables. These easy-to-follow steps will ensure your tables are optimized to handle large data sets and multiple queries. Providing a fast response to every query.

Step 1: Use a database called information_schema.

SHOW DATABASES;
USE information_schema;

Output:

Step 2: Finding the data_free column for the specific schema (remove the WHERE clause for all databases):

INFORMATION_SCHEMA is a database that contains the data from all the other databases. So we check for the free data directly from this schema. By using the below command, we will find all tables with any free data.

SELECT table_name,  data_free
FROM information_schema.tables
WHERE data_free > 0
ORDER BY data_free desc;

This query lets us see unused space for all tables.

Output:

Here, we use the SELECT command to specify the column names. In FROM command, we specify the table from the schema using a period(“.“), while by using the WHERE clause we only get the free data for those that have any values in that column. At Last, we sort the data_free column in descending order.

Or you can check information for any specific table using the below command. 

SHOW TABLE STATUS LIKE “<table_name>” \G

We will look at the stats for the password_history table.

SHOW TABLE STATUS LIKE "password_history" \G

Step 3: Use the OPTIMIZE function that is provided by MySQL and use it on the table that we need to optimize.

OPTIMIZE TABLE password_history;

The command OPTIMIZE TABLE works with InnoDB, and the output shown below is the output it gives for all InnoDB tables.

Output:

You need to optimize MySQL tables to get the best out of MySQL databases. Optimizing tables and databases makes them faster in MySQL.

In this article, we will learn how to use important methods to improve the speed, size growth, and safety of MySQL tables. Find out the main actions that make operations smooth, queries quicker, and a strong base for good data management.

Before learning the methods of table optimization, let’s quickly go through the reasons for optimizing the tables.