If you are looking to improve the performance of MySQL databases in your software, you may need to know all the differences between InnoDB and MyISAM, two well-known types of MySQL storage engines. And if you are about to choose one of them, we believe you would like to find out what each of them has to offer and which one will suit your software better. That’s what our article is going to help you with.
CONTENTS
1. What is the MyISAM engine?
2. What is the InnoDB engine?
3. The main differences between MyISAM and InnoDB
4. MyISAM vs InnoDB: a detailed comparison
5. How to check whether you are using MyISAM or InnoDB
6. When it is better to use MyISAM and when you should use InnoDB
7. How to convert MyISAM to InnoDB (and vice versa)
8. Conclusion
What is the MyISAM engine?
In the past, MyISAM was considered to be the better choice, quick and well-optimized for heavy read operations. It was used for a lot of different things, from performing data analysis to developing simple content management systems for forums or building smaller search engines. Some developers created tools and solutions that were tailored to MyISAM, since it was thought to be simpler than InnoDB. Actually, many developers began their journey to the MySQL world with the use of MyISAM. However, in 2009 it was replaced with InnoDB as the default MySQL storage engine. Still, it offers many useful extensions to MySQL developers.
What is the InnoDB engine?
The InnoDB general-purpose storage engine for MySQL has evolved from a storage subsystem to a full-fledged storage engine. It was the default MySQL engine from version 5.6 onwards, thanks to its combination of high performance and reliability. It can be easily integrated into software. While MySQL determines the way in which data is saved to a database, the InnoDB storage engine stores the data on a disk or keeps it in the main memory for quick access. When a transaction is complete, data is written to the storage medium according to the transaction. It is important to note that incomplete changes are not stored in the database.
The main differences between MyISAM and InnoDB
Overall, MyISAM is an older and less efficient storage engine than InnoDB. The most commonly noted differences between these two engines are as follows: InnoDB is more stable, faster, and easier to set up; it also supports transactions. It is the default storage engine of choice for a reason, and you can use the features of InnoDB with no need to worry about compatibility issues. If you need to store large amounts of data or ensure that transactions will work correctly, choose InnoDB.
The MyISAM engine is not very good at storing large amounts of data, because it stores everything in a single table. When you need to add data to the database, you have to lock the entire table, which can cause your database to stop working until it is unlocked. In the InnoDB engine, each row is stored separately in a separate table. This means that when you insert data into a MySQL database, you do not need to lock all rows.
Now let’s proceed to a more detailed overview of these storage engines.
MyISAM vs InnoDB: a detailed comparison
Now let’s make a face-to-face MyISAM and InnoDB storage engine comparison. The former stores data in its memory, while the latter uses disk space for storage. Both are considered fast, but one is better than the other, depending on the situation you are dealing with. For example, MyISAM is faster when you insert, update, or delete large amounts of data, as the database doesn’t have to use disk space. However, InnoDB is more efficient at indexing and retrieving information, and is generally a far more advanced and preferred option.
1. Storage engine type
Let’s begin with MySQL engine types. InnoDB is a transactional storage engine, while MyISAM belongs to the non-transactional category. The former means that, if your data manipulation involved a transaction, then a rollback will be triggered automatically in case the transaction is not completed. This is obviously recommended over MyISAM, which does not support transactions, and with which, in case of need, you will have to roll back changes manually.
2. Transactions
InnoDB is a storage engine for online transaction processing. It stores the actual data in a separate area called the InnoDB buffer pool. This area allows MySQL databases to locate and access data efficiently and quickly. The InnoDB engine is a key to the stability and performance of a MySQL database. As opposed to it, the MyISAM engine is an older and less popular storage engine for storing tables.
3. ACID Properties
Atomicity, consistency, isolation, and durability are the well-known ACID properties. In case of error or system failure, compliance with these properties guarantees that the transaction will be completed. And while InnoDB offers full compliance, MyISAM does not.
4. Performance comparison
InnoDB supports transactional properties. It offers a higher speed of code writing with rollbacks and commits. The performance of InnoDB with large volumes of data is better than that of MyISAM. Meanwhile, MyISAM is faster to read, does not support transactional properties, and its performance with large volumes of data leaves a lot to be desired in comparison with InnoDB.
5. Foreign key support
A foreign key of a table is a set of attributes that refers to the primary key of another table. A table may have multiple foreign keys, and each foreign key can have a different parent table. With MyISAM, you cannot add foreign key constraints, while InnoDB fully supports this feature.
6. Table-level locking vs row-level locking
When the locking option is activated, two or more users cannot modify the same data simultaneously. This feature preserves the validity of data. Table locking is the default method of locking for MyISAM, which allows you to modify tables in a single session. The tables are locked in a certain order. The table locking method can be used for read-only databases that do not require a lot of memory.
For InnoDB, the default method of locking is row-level locking. Rows are locked to support multiple sessions on selected rows. It is vastly preferable for databases that require more than one active user.
In a nutshell: InnoDB offers flexible row-level locks while MyISAM can only do table-level locks. The crash recovery in InnoDB is also superior.
7. Caching and indexing
Now a few words about caching and indexing in InnoDB and MyISAM. In InnoDB, there is a large buffer pool that can be used to store both data and indexes. As for MyISAM, there is a key buffer that is used for indexes. Meanwhile, the main caching mechanism is a cache key, which uses MYI files to cache pages.
How to check whether you are using MyISAM or InnoDB
If you don’t know what storage engine is being currently used in your MySQL database, let’s check it and see. The first way is to launch MySQL Shell and run the SHOW DATABASES; command, then locate and select the required database with the USE database_name; command.
After that, a confirmation message will be shown that the database is located. Now you can use the SHOW CREATE TABLE command to display the table and storage engine information. It looks as follows:
SHOW CREATE TABLE database_name.table_name;
The output will show the default storage engine for the required table.
There is an easier way to do it if you are using dbForge Studio for MySQL. In Database Explorer, simply right-click the required table and click Properties on the shortcut menu. As you can see, we have “INNODB” in the Engine field of the Properties window in the lower-left corner of the screen.
When it is better to use MyISAM and when you should use InnoDB
If a MyISAM table has an issue, it will only affect the table. If there are any problems with other tables or databases, they will not affect the operations of other tables or databases. Therefore, MyISAM can be recommended for servers with multiple sites.
The InnoDB storage engine is recommended for frequent operations with tables. It has better performance, better functionality, and can also substantially reduce memory usage on the server. Naturally, it is generally preferred over MyISAM. Still, it is worth noting that, because issues with InnoDB tables can cause data loss in other InnoDB tables, it is important to make sure you have enabled automatic backups.
How to convert MyISAM to InnoDB (and vice versa)
Converting from MyISAM to InnoDB can be rather helpful if, for example, you encounter an older MyISAM table that has to be converted to match your current InnoDB environment.
How to convert all MyISAM tables to InnoDB
If you want to convert all tables in a required database, you can do it safely via phpMyAdmin.
1. Once you launch it and select the required database, you can run the following query against it, having replaced database_name with the actual name of your database using MySQL concatenation:
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'database_name';
For example, this is what it’s going to look like with the sakila database:
2. After you submit this query, you will see the list of all tables to be converted to InnoDB.
3. Click +Options above the results, select Full texts and click Go in the lower-right corner of the screen.
4. Select the Show all check box and copy all queries using Copy to clipboard in the Query results operations box.
5. Paste the results into the text editor and copy all the lines that start with ALTER TABLE to your clipboard.
6. Click the SQL tab above the results and paste the ALTER TABLE statements into the text field, then click Go once again. All the tables in your database will be converted to InnoDB.
How to convert a single MyISAM table to InnoDB
If you want to convert a single table from MyISAM to InnoDB, run the ALTER TABLE command, having replaced database_name and table_name with the actual database and table names:
ALTER TABLE database_name.table_name ENGINE=InnoDB;
You might as well do it using the abovementioned dbForge Studio for MySQL:
How to convert InnoDB to MyISAM
Similarly, you may want to convert an InnoDB table to MyISAM. The method is the same as in the previous case:
ALTER TABLE database_name.table_name ENGINE=MyISAM;
Please note that your InnoDB table may have foreign key constraints, which are not supported by MyISAM. If you try to convert a table with such constraints, you will encounter an error.
Conclusion
We hope this article helped you better understand the most crucial differences between these two MySQL storage engines, and eventually allowed you to make the optimal choice based on your needs and requirements.
And if you are looking for an all-encompassing MySQL IDE that works with different storage engines, you might as well try dbForge Studio for MySQL. It allows you to easily create and execute queries, develop and debug stored routines, automate database object management, compare and synchronize MySQL databases, analyze table data, and much more. Its rich functionality is delivered under a sleek and intuitive user interface.
