How to Optimize MySQL Tables

Sometimes you may find that MySQL queries and performance has slowed down over time. In such cases, you may need to optimize MySQL tables. There are several different types of optimizations available for MySQL tables. In this article, we will learn the different MySQL optimizations and also when to use them. That is very useful to database management and maintenance.

How to Optimize MySQL Tables

If your MySQL database takes up a lot more space than it should, or takes a lot more time to process queries, than it should, then you should try to optimize them.

But it is important to note that database optimization can take a long time, depending on the size of tables and amount of optimization needed. Since transactional tables are generally the ones that need optimization, you need to plan in advance before you run the following optimizations, since they will most likely lock your tables when they are running.

So before you optimize tables, you can also try the following tricks to see if it improves performance

If you find that your tables work properly even without indexing for some time, then you can drop the index from a table, optimize it, and then add the index back. It might be faster in some cases.
It is also important to know which value to optimize. Generally, it is the secondary indexes that cause problems and not the primary index. They get fragmented over time. In such cases, you should optimize only secondary indexes and not the primary ones.

1. Determine tables to be optimized

First connect to your database. Replace <database_name> with your database name.

mysql> use <database_name>

Next, run the following command, on your table that you think might be suitable for optimization. Replace <table_name> with your table’s name,

mysql> show table status like "<table name>" \G

The output will list many values. In the output, look for two parameters mentioned below

Data_length – amount of total space occupied by the tables
Data_free – amount of unallocated space in database table. It helps us determine which table requires optimization. By default, MySQL allocates a certain space for each table.

If you want to list the unallocated space for each table, run the following command. Replace <schema_name> with your database schema name.

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

It will display table name, the space occupied and free space for each table in your database. All this metadata information is stored in information_schema table, for each table in all your databases in the system. If you want to see the above information for all tables in all databases, exclude the WHERE clause in it.

Now the above query’s output will be in no. of bytes. If you want to see the data_length and data_free in MB (megabytes), modify the above query as shown below.

mysql> select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
       from information_schema.tables
       where table_schema='<schema name>'
       order by data_free desc;

2. How to Optimize MySQL Tables

Now we will look at how to optimize MySQL tables. Here is the syntax for optimizing tables. Replace <table_name> with the name of table that you want to optimize.

mysql> optimize table <table_name>;

The optimize command, locks the table, creates a temporary copy of it, optimizes this temporary copy and replaces the original table with the temporary table, and releases the lock added earlier. When optimization is a complete, the above command will give a simple output displaying the results.

Here is the command to optimize multiple MySQL tables. You need to list them one after the other in a comma-separated manner.

mysql> optimize table <table_name1>, <table_name2>, <table_name3>;

You can also optimize MySQL tables from terminal, with the following command. Replace <schema> with your database schema, <table> with table name you want to optimize, <username> with database username, and <password> with database password.

mysql> sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

During optimization, MySQL will free up unused allocated memory as well as improve database’s overall memory. Once the optimization is complete, you can run the SQL queries mentioned in step 1, to see the difference.

In this article, we have learnt how to optimize tables in MySQL. In most cases, MySQL automatically manages databases and queries very well, without any need for optimization. However, if you are using really large tables, you may need to optimize them once in a while, especially if they are transactional tables that are written frequently. Nevertheless, you need to be careful before you run the optimization since it can lock the table for a long time. So optimize your MySQL tables only after detailed analysis of its query performance and space consumption.

How to Optimize MySQL Tables