How to Convert a Database from MyISAM to InnoDB

Databases are one of the most important infrastructure of modern web applications. Whether you are just getting started with database or a seasoned pro, learning how to optimize your database for maximum performance is one of the most important skills.

Although there are a ton of methods and techniques for optimizing database performance, choosing the right storage engine for your MySQL database is important.

In this article, you will understand the various features of MySQL storage engines: MyISAM and InnoDB, check your current storage engine, and learn how to convert your storage engine.

What is MyISAM and InnoDB?

In simple terms, MyISAM and InnoDB refers to two types of database storage engines supported by MySQL databases. A storage engine is a software component that a database management system used to perform database CRUD operations from a database.

MyISAM Storage Engine

MyISAM or Indexed Sequential Access Method is one of the most popular storage engines used in MySQL databases. It was shipped with MySQL < 5.5 as the default storage engine until 2009 when it was replaced by InnoDB engine.

MyISAM is based on the ISAM algorithm with support for features such as B-tree indexing, Data Compression using row format, Data Encryption, Index caches, etc.

Due to the scope of this tutorial, we cannot discuss all the features and components of MyISAM engine. You can check out the documentation below to discover more.

https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

InnoDB Storage Engine

As mentioned, InnoDB superseded MyISAM as the default storage engine for MySQL in version > 5.5. It is a powerful, general-purpose storage engine that is capable of handling large data transactions. InnoDB provides a suitable balance between reliability and high-performance.

InnoDB supports all the features of MyISAM with additional such as Hash Indexing, Foreign key support, MVCC, etc.

Check out InnoDB documentation in the resource below:

https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

Check your Database Storage Engine

It is best to understand which database engine you are using. This can help you avoid errors due to operations that are not supported by a given engine.

Start by logging in to your MySQL cluster:

mysql -u root -p

Once logged in, select the database whose engine you wish to determine:

USE <database_name>

Once you have switched to the target database, run the query below to determine your engine.

SHOW CREATE TABLE <table_name>

Replace the table_name with a table in the target database.

The query above should return detailed information about the create instructions for the target table. An example output is as shown below:

| film  | CREATE TABLE `film` (
  `film_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `description` text,
  `release_year` year DEFAULT NULL,
  `language_id` tinyint unsigned NOT NULL,
  `original_language_id` tinyint unsigned DEFAULT NULL,
  `rental_duration` tinyint unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

In the example above, we can see the target database is using Engine=InnoDB.

Convert MyISAM to InnoDB

We can use the ALTER TABLE command to convert the storage engine from MyISAM to InnoDB.

The command is as shown:

ALTER TABLE <database>.<table_name> ENGINE=InnoDB;

The query above should convert the specified table to use InnoDB storage engine.

You can run the same query to revert to MyISAM.

ALTER TABLE <database>.<table_name> ENGINE=MyISAM;

Conclusion

In this post, you learned how to convert a database from MyISAM to InnoDB storage engine and vice versa.

Let us know if you’d like to see a detailed comparison of MyISAM and InnoDB in the comments below.