Changing a Table’s Storage Engine in MySQL

1. Overview

MySQL provides several engines, each suitable for different needs. Changing the table’s storage engine in MySQL enhances the performance and optimizes storage based on specific use cases. In particular, understanding how to switch between these engines enables us to tailor database performance and behavior of application requirements.

In this tutorial, we’ll demonstrate different ways to check the storage engine of a table. Furthermore, we’ll explore how to change the storage engine in MySQL using the Student table from the simplified Baeldung University database.

2. What Is a MySQL Storage Engine?

MySQL’s storage engines are responsible for how data is stored, retrieved, and managed. We can check the engines supported by MySQL with the use of the SHOW command:

SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.004 sec)

The output shows all the engines available in MySQL.

InnoDB is the default storage engine. It offers support for ACID-compliant transactions, foreign keys, and row-level locking.

Some other ones of note are MyISAM and MEMORY. MyISAM provides fast read operations and is suitable for read-heavy applications, but lacks support for transactions and foreign keys. The MEMORY engine stores all data in RAM and provides fast access but at the cost of volatility.

3. Checking the Storage Engine of a Table

Before changing the storage engine, it’s important to know how to check the current engine used by a table. For example, let’s query information_schema.tables and check the storage engine of the Student table:

SELECT 
    engine
FROM
    information_schema.tables
WHERE
    table_schema = 'University'
    AND table_name = 'Student';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.003 sec)

Alternatively, we can use the SHOW TABLE STATUS command to provide detailed information about the Student table, including the storage engine:

SHOW TABLE STATUS LIKE 'Student';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Student | InnoDB |      10 | Dynamic    |   14 |           1170 |       16384 |               0 |        16384 |         0 |           NULL | 2024-07-18 16:48:49 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.005 sec)

The output shows that the storage engine of the Student table is InnoDB, our default.

4. Changing the Storage Engine

Switching the storage engine for a table in MySQL is straightforward. We use the ALTER TABLE statement to achieve this. For example, let’s change the storage engine of the Student table to MyISAM:

ALTER TABLE Student ENGINE=MyISAM;
Query OK, 25 rows affected (0.040 sec)
Records: 25  Duplicates: 0  Warnings: 0

The command alters the Student table to use the MyISAM engine, optimizing it for read-heavy operations.

Now let’s check the storage engine of the Student table:

SELECT 
    engine
FROM
    information_schema.tables
WHERE
    table_schema = 'University'
    AND table_name = 'Student';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0.003 sec)

The output shows that the change was successful.

We can of course revert to the InnoDB engine by simply indicating the engine type:

ALTER TABLE Student ENGINE=InnoDB;
Query OK, 25 rows affected (0.041 sec)
Records: 25  Duplicates: 0  Warnings: 0

This command changes the Student table back to InnoDB, ensuring ACID compliance and support for foreign keys.

5. Conclusion

In this article, we’ve explored the different methods to check and change the storage engine of a table in MySQL. Understanding this process allows for database optimization for specific use cases, thereby, improving performance and functionality.