How to Convert MyISAM to InnoDB in MySQL Storage Engine

MySQL has a range of available storage engines that can help define how data is stored, accessed, and managed. The two most widely used storage engines are MyISAM and InnoDB.  MyISAM is one of the oldest storage engines, whereas InnoDB is the default and most popular storage engine. It is famous for its read-heavy tasks speed, whereas InnoDB is created to handle complex operations. MyISAM is suitable for less complex applications, while InnoDB is ideal for modern applications that need to perform many operations at the same time.

This write-up illustrates the difference between MyISAM and InnoDB and how to convert MyISAM to InnoDB in MySQL.

Which MySQL Storage Engine Should You Use? MyISAM or InnoDB

In MySQL, InnoDB is the default and most popular storage engine. It is designed for applications that require data integrity, reliability, and support for complex transactions. Unlike MyISAM, InnoDB supports ACID-compliant transactions, which means you can commit or roll back changes. It ensures data accuracy and safety. 

InnoDB only locks the rows that are being changed, not the whole table. This helps improve performance, especially when many users are accessing or modifying the database at the same time. It also supports foreign keys, allowing us to set relationships between tables and ensure data stays consistent across the database. 

MyISAM is one of the classical storage engines in MySQL. It was primarily designed for applications that focus on read-heavy operations. It is fast for queries that do not require complex transactions or relationships between tables. However, it has some limitations, such as not supporting transactions, foreign keys, and using table-level locking. Even with these limitations, MyISAM is still helpful in cases where fast reading is more important than handling complex transactions.

Checking the Current Storage Engine 

You can check the current storage engine of a table using either MySQL Shell or MySQL Workbench. You can identify which storage engine is being used by a specific table using both these methods, such as InnoDB or MyISAM. 

Using MySQL Shell 

Select a MySQL database and a table, and use the following command to check the current storage engine via the command line:

SHOW CREATE TABLE [databaseName].[tableName]\G

Replace databaseName and tableName with actual names, such as example and users, respectively:

SHOW CREATE TABLE example.users\G

Using MySQL Workbench

To check the current storage engine using MySQL Workbench, connect to your database, and expand the schemas section. Choose the target database, and then expand the Tables section:

Right-click on the targeted table and choose Table Inspector:

Under the Info tab, look for the Engine field, which will show something like InnoDB or MyISAM:

MySQL Workbench allows us to check the current MySQL storage engine using a query. For this purpose, open the query tool and execute the following query:

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'example';

Converting MyISAM Tables to InnoDB

To convert a table from MyISAM to InnoDB or vice versa, you need to change its storage engine. This can be done through tools like MySQL Shell or MySQL Workbench. You can also run SQL queries in phpMyAdmin to convert MyISAM to InnoDB.

Convert MyISAM to InnoDB Using MySQL Shell

You can convert the MySQL storage engine of a table from MyISAM to InnoDB by running the ALTER TABLE statement in the MySQL command line or any SQL client. For this purpose, use the following syntax:

ALTER TABLE [databaseName].[tableName] ENGINE=InnoDB;

Let’s replace the databaseName and tableName with actual database and table names:

ALTER TABLE example.users ENGINE=InnoDB;

The query has been executed successfully:

To verify the conversion of the MyISAM storage engine to InnoDB, use the following command:

SHOW CREATE TABLE example.users\G

You can switch back to the MyISAM storage engine by running the command given below:

ALTER TABLE [databaseName].[tableName] ENGINE=MyISAM;

Replace databaseName and tableName with the real names of your database and table:

ALTER TABLE example.users ENGINE=MyISAM;

Convert MyISAM to InnoDB Using MySQL Workbench

You can also use a GUI method to convert a MyISAM table to InnoDB through MySQL Workbench. Using the method, you can easily convert MyISAM to InnoDB in a few steps.

Launch MySQL Workbench and connect to your MySQL server. Expand your database schema from the Navigator panel and then expand the Tables section:

After this, right-click the MyISAM table you want to convert, and select the “Alter Table…” option:

Now in the Alter Table window, look for the Storage Engine dropdown, change it from MyISAM to InnoDB, and click the “Apply” button to save changes.

When you click on the Apply button, a new window pops up which shows the SQL command (e.g., ALTER TABLE yourTable ENGINE = InnoDB;). Review it and click the “Apply” button again to execute the query:

Next, you will be prompted that the script was successfully applied to the database. Click the Finish button to complete the MyISAM to InnoDB conversion:

MyISAM Vs InnoDB

InnoDB and MyISAM are two commonly used storage engines in MySQL. Let’s go through the following points to understand the InnoDB and MyISAM Differences:

• InnoDB uses row-level locking. It means InnoDB locks only the rows that a query accesses. It allows better performance in multi-user environments. However, MyISAM locks the entire table, which can cause performance issues when many users are making changes.
• InnoDB supports foreign keys and enforces relationships between tables, which helps maintain data accuracy. However, MyISAM does not support this feature.
• MyISAM does not support transactions, which makes it less suitable for critical data operations. On the other hand, InnoDB allows transactions to safely commit or roll back changes.
• InnoDB is more reliable as compared to MyISAM because it uses transaction logs that help recover data in case of a crash. However, MyISAM lacks this built-in recovery feature.

Therefore, InnoDB is the go-to choice for applications that need to handle a high volume of data transactions securely, such as e-commerce sites, banking systems, and other business-critical applications.

That’s all about InnoDB vs MyISAM and how you can convert a MySQL storage engine from classical MyISAM to the modern InnoDB.

Final Thoughts

In MySQL, MyISAM and InnoDB are the most widely used storage engines. These storage engines offer distinct pros and cons. Selecting the right storage engine in MySQL is important for ensuring your application runs efficiently and reliably. MyISAM is a simple and lightweight storage engine. It is designed for tasks that involve a lot of reading, and is a good choice for basic applications. 

MyISAM does not support transactions or relationships between tables. It does not support features like foreign keys and transactions, and uses table-level locking. Therefore, MyISAM can cause performance issues in multi-user environments. However, in MySQL, the default and most commonly used storage engine is InnoDB. It is created to deal with complex tasks and ensure data integrity. InnoDB is transactional, supports row-level locking, and foreign key constraints.

If your current setup uses MyISAM but your application has become complex, you can easily convert your tables to InnoDB using either MySQL Shell or MySQL Workbench. It enables you to take advantage of InnoDB’s reliability, better concurrency, and improved data management.

FAQ