Change Storage Engine

MySQL storage engines are used for maximizing the performance of the database. It handles create, read, and update operations for storing and managing the information in a database. In this article, we are going to learn how to change the storage engines in MySQL.

The following are various storage engines supports in MySQL that provide different capabilities and characteristics:

• ISAM
• MyISAM
• MERGE
• InnoDB
• MEMORY (HEAP)
• ARCHIVE
• BDB
• CSV
• FEDERATED

If we want to know which storage engines our MySQL server supports, we need to use the below query.

mysql> SHOW ENGINES;  

It returns the below output:

The Support column value indicates whether an engine can be used or not in the current server. A value of YES, NO, or DEFAULT shows that table type is available, not available, and available & currently set as the default storage engine.

How to show the current storage engine of a table?

We can get the current storage engine of a table in several ways that are discussed below:

1. The first way to show the current storage engine of a table is in the information_schema database. For example, if we have a table named students in the mystudentdb database, we can use the following query to get the current storage engine:

mysql> SELECT engine FROM information_schema.tables  

WHERE table_schema = 'mystudentdb' AND table_name = 'students';

We will get the below output:

2. The second way to show the current storage engine of a table is to use the SHOW TABLE STATUS command. For example, if we want to get the storage engine of a table named students, we can use the query as follows:

mysql> SHOW TABLE STATUS LIKE 'students';  

We will get the below output where we can see the current storage engine in the red rectangular box:

3. The third way to show the current storage engine in a table is to use the SHOW CREATE TABLE command. For example, if we want to get the storage engine of a table named students, we can use the query as follows:

mysql> SHOW CREATE TABLE students \G;  

We will get the below output where we can see the current storage engine in the red rectangular box:

How to change the storage engine in MySQL?

We can see that MySQL shows the ‘students’ table uses the InnoDB storage engine. Sometimes, we want to change the storage engine. In that case, we can use the following statement:

ALTER TABLE table_name ENGINE engine_name;  

We have already seen all storage engines currently supported in the MySQL server using the SHOW ENGINES statement. If we want to change the storage engine of the ‘students’ table from InnoDB to any other engine, we can use the statement as follows:

mysql> ALTER TABLE students ENGINE = 'MYISAM';  

After executing this statement, we will get the below output:

We can verify it by showing the current storage command again. See the below output where InnoDB engine changed to MyISAM engine:

How to change the storage engine in MySQL Workbench?

We first launch the tool and log in with the username and password to change the desired table’s storage engine in MySQL Workbench. Now, we need to do the following steps for changing the storage engine:

1. Go to the Navigation tab and click on the Schema menu where all the previously created databases available. Select your desired database (for example, mstudentdb). It will pop up the following options.

2. Expand the Tables sub-menu and select the table which storage engine you want to change. After selecting a table, right-click on the selected table (for example, students), and then click on the Alter Table option. Clicking on the Alter Table option, we will get the screen as below:

3. Next, click on the Engine option shown in the previous step’s red rectangular box; we will get the names of all currently supported storage engine in MySQL. See the below screen:

4. Now, select the desired storage engine from the given option and click on the Apply button. It will give the below screen:

5. Finally, we will click on the Apply button again, and if no error is found, click on the Finish button to complete the process of changing the storage engine.

In this tutorial, we have learned how to show the storage engine currently supported in MySQL, get a table’s current storage engine, and change one engine to a different engine.