How to Optimize All the Tables in MySQL

1. Overview

MySQL is a relational database that supports different types of storage engines, with InnoDB and MyISAM being the main two. Table fragmentation could result in a table taking up more storage space than it should. Furthermore, table fragmentation could result in a query taking more time than it should, especially for queries involving a full-table scan. We can optimize a table to reorganize the physical storage of table data and associated index data to reap the benefits of reduced storage space and improved efficiency of I/O operations.

InnoDB doesn’t support table optimization in the same way the legacy storage engine MyISAM does. Because InnoDB stores data differently than MyISAM, fragmentation isn’t as much of an issue.

In this tutorial, we’ll discuss different methods to optimize all our tables in MySQL at once.

2. Setting up Tables

To demonstrate, we’ll use tables from the Baeldung University database. Since optimization is a little different for MyISAM than for the default InnoDB engine, let’s imagine a separate database, University_myisam, and create the same set of tables with the storage engine as MyISAM using a sample script available in our GitHub repository along with the other scripts for this article.

To optimize all the tables in a database, we’ll first need to get all the table names. The SHOW TABLES command can help us with this:

SHOW TABLES;
+----------------------+
| Tables_in_university |
+----------------------+
| course               |
| department           |
| exam                 |
| faculty              |
| prerequisite         |
| program              |
| registration         |
| specification        |
| student              |
| teaching             |
+----------------------+

3. Using the OPTIMIZE TABLE Statement

We use OPTIMIZE TABLE to defragment a table, reorganize its storage, and improve I/O efficiency. This statement works differently with each of the storage engines, InnoDB and MyISAM. Let’s discuss its use with each engine separately.

3.1. With MyISAM

OPTIMIZE TABLES is primarily a tool built to address fragmentation in MyISAM tables, so let’s start with that storage engine.

To demonstrate this, let’s switch to the University_myisam database we created for MyISAM tables and run the OPTIMIZE TABLE statement, listing all tables:

USE University_myisam;

OPTIMIZE TABLE Course, Department, Exam, Faculty, Prerequisite, Program,
Registration, Specification, Student, Teaching;
+---------------------------------+----------+----------+----------+
| Table                           | Op       | Msg_type | Msg_text |
+---------------------------------+----------+----------+----------+
| university_myisam.course        | optimize | status   | OK       |
| university_myisam.department    | optimize | status   | OK       |
| university_myisam.exam          | optimize | status   | OK       |
| university_myisam.faculty       | optimize | status   | OK       |
| university_myisam.prerequisite  | optimize | status   | OK       |
                                 ...                               |
+---------------------------------+----------+----------+----------+

For MyISAM, OPTIMIZE TABLE repairs the table if needed, sorts index pages if they aren’t sorted, and updates the table’s statistics if they aren’t up to date.

3.2. With InnoDB

We’ll use the same OPTIMIZE TABLE script for our InnoDB tables, but we’ll also use the \G query terminator to format output vertically for better readability:

OPTIMIZE TABLE Course, Department, Exam, Faculty, Prerequisite, Program,
Registration, Specification, Student, Teaching\G
*************************** 1. row ***************************
   Table: university.course
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: university.course
      Op: optimize
Msg_type: status
Msg_text: OK
*************************** 3. row ***************************
   Table: university.department
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 4. row ***************************
   Table: university.department
      Op: optimize
Msg_type: status
Msg_text: OK
                          ...

The risk of fragmentation in InnoDB tables is negligible. Because of that, OPTIMIZE TABLE table_name gets mapped to an ALTER TABLE table_name FORCE statement to perform a no-op alter operation that rebuilds the table. This rebuild updates index statistics and frees unused space in the clustered index.

We should plan on optimizing InnoDB tables during off-peak hours, especially on production systems. This is because each table is locked while it’s being optimized, which could create issues relating to sessions having to wait to acquire a lock.

4. Using the mysqlcheck Utility

We should use the mysqlcheck table maintenance program to optimize our tables when the number of tables is very large or when the table names aren’t known. The mysqlcheck program performs multiple operations by default. Further, it checks, repairs, optimizes, or analyzes tables. However, we can specify the –optimize (or -o) option when we only want to optimize tables.

4.1. Single Database

Let’s optimize all the tables in the University database, as an example:

$ mysqlcheck -u root -o University
university.course
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
university.department
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
                                   ...

When we don’t specify any table names after the database name, it optimizes all the tables in the database.

Also, we can specify selected table names when we want to optimize only a few of the tables:

$ mysqlcheck -u root -o University Department Prerequisite Program

This time, it only optimizes these three tables if needed.

When the tables are already optimized, it doesn’t run the optimization operation again. This is true in our example if we’ve already optimized with the OPTIMIZE TABLE statement.

Similarly, we can optimize all tables in the MyISAM storage engine:

$ mysqlcheck -u root -o University_myisam
university_myisam.course                           Table is already up to date
university_myisam.department                       Table is already up to date
                                    ...

If a table is already up to date, it doesn’t run the optimize operation again, as indicated in the example output.

4.2. Multiple Databases

The mysqlcheck program supports the optimization of all tables in multiple databases. The main advantage of using mysqlcheck over the OPTIMIZE TABLE statement is that we don’t specify individual table names even when all we want is to optimize all tables.

Let’s optimize all the tables in the University and University_myisam databases in a single program call:

$ mysqlcheck -u root -o --databases University University_myisam

Again, it optimizes tables only if they need to be optimized.

4.3. All Databases

We can use the mysqlcheck program to optimize all the tables in all the databases. Let’s demonstrate with an example:

$ mysqlcheck -u root -o --all-databases

We should use –all-databases sparingly when the number of databases and tables is large since the optimization could take an inordinate amount of time.

5. Conclusion

In this article, we learned about optimizing all the tables in a MySQL database with a single SQL statement or a single program call. We should use the OPTIMIZE TABLE statement only when the number of tables is small and the table names can be found. We should use the mysqlcheck program when we want to optimize all tables in a single, multiple, or all databases.