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. MyISAM vs InnoDB: Which is better to choose?
7. MyISAM or InnoDB: Full performance comparison
8. How to convert MyISAM to InnoDB (and vice versa)
9. 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.
MyISAM vs InnoDB: Which is better to choose?
Now, let’s make a MyISAM vs InnoDB storage engine comparison. The former stores data on disk and leverages the operating system’s cache for faster access, while the latter also stores data on disk but employs its buffer pool for caching. Both are considered fast, but one is better than the other, depending on the situation you are dealing with. Let’s look for examples and specific use cases.
When should you choose MyISAM?
MyISAM is a non-transactional storage engine that prioritizes fast read operations and simple data management. It may be the right choice in the following cases:
- Heavy read operations. MyISAM is optimized for fast SELECT queries, making it suitable for applications with frequent read operations and fewer updates.
- Large-scale data inserts and updates. Since MyISAM does not use transactional logging, inserting, updating, or deleting large amounts of data can be faster than InnoDB in certain scenarios.
- Read-only or low-concurrency databases. MyISAM’s table-level locking makes it inefficient for multiple concurrent writes but works well for read-heavy applications with minimal updates.
- Minimal hardware constraints. MyISAM has lower memory requirements, making it a better choice for environments with limited system resources.
When should you choose InnoDB?
InnoDB is a transactional storage engine designed for high-performance, multi-user applications that require data integrity and concurrency control. It is the preferred choice when:
- Transactional support is required. InnoDB ensures ACID compliance, meaning transactions can be rolled back safely in case of failure.
- High concurrency is needed. With row-level locking, InnoDB allows multiple users to update different rows simultaneously without blocking the entire table.
- Data integrity is crucial. Foreign key constraints enforce relationships between tables, preventing orphaned records and ensuring referential integrity.
- Crash recovery is a priority. InnoDB’s built-in crash recovery mechanisms protect data even in case of system failures.
- Efficient indexing and caching are needed. The InnoDB buffer pool optimizes data retrieval and index management, improving performance for complex queries.
- Scalability and performance with large datasets. InnoDB is better suited for handling massive datasets efficiently, making it ideal for enterprise applications and high-traffic databases.
MyISAM or InnoDB: Full performance comparison
Ten years ago, MyISAM was known for its better performance than InnoDB; however, as time flies, InnoDB has evolved to the point where it is considered better in many areas. When comparing MyISAM vs InnoDB performance, the choice largely depends on the specific needs of your application. However, there are still several general metrics, such as READ and WRITE performance, that we can compare. Let’s check them out.
Read performance
MyISAM excels in read-heavy workloads due to table-level locking, which allows fast SELECT queries when no concurrent writes are happening. InnoDB, despite offering row-level locking, may experience disk I/O overhead due to MVCC (Multiversion Concurrency Control). It maintains transactional integrity by recording changes in undo logs, which can slow down simple read operations.
Conclusion: MyISAM is faster for pure read-heavy applications, but InnoDB’s caching mechanisms often compensate for its additional overhead.
Write performance
MyISAM locks entire tables during writes, which can cause bottlenecks in high-concurrency environments. While it’s efficient for bulk inserts or batch updates, frequent write operations can slow down performance. On the other hand, InnoDB supports row-level locking, allowing multiple transactions to write to different rows simultaneously. It also supports commit and rollback operations, making it ideal for transactional databases.
Conclusion: InnoDB outperforms MyISAM in write-intensive workloads due to its superior concurrency management.
Indexing
MyISAM supports full-text indexing out of the box, making it a strong choice for search-heavy applications. Indexes are stored separately from table data, improving lookup speed.
InnoDB, however, has also supports full-text indexing since MySQL v.5.6.
Conclusion: The difference between InnoDB and MyISAM isn’t in their indexing capabilities: MyISAM supports full-text indexing out of the box, but this feature is also available within InnoDB.
Concurrency
MyISAM relies on table-level locking, meaning even a single row update locks the entire table, slowing down concurrent operations. This can be a major limitation for high-traffic applications.
InnoDB utilizes row-level locking, preventing unnecessary contention. Multiple transactions can modify different rows at the same time without blocking the entire table.
Conclusion: InnoDB is the clear winner for multi-user environments, as it minimizes bottlenecks and allows higher throughput.
Disk space usage
MyISAM tables are generally smaller, as they don’t store transactional metadata. However, they can suffer from fragmentation, requiring periodic optimization.
InnoDB dynamically manages disk space and automatically reclaims unused storage, but its ibdata1 system tablespace can grow over time, requiring careful management.
Conclusion: The difference between MyISAM and InnoDB is evident in their disk space usage: MyISAM consumes less disk space but requires manual maintenance, whereas InnoDB handles storage more efficiently and dynamically.
When comparing MyISAM vs InnoDB performance, the choice largely depends on the specific needs of your application. MyISAM excels in read-heavy environments, offering faster SELECT queries due to its table-level locking.
However, when deciding between InnoDB or MyISAM, consider that InnoDB compensates for its additional disk I/O overhead with efficient caching mechanisms, making it better suited for environments requiring both reads and writes. For write-intensive workloads, InnoDB’s row-level locking and transaction support provide superior concurrency and management of multiple write operations.
The difference between InnoDB and MyISAM in a nutshell
| Feature | MyISAM | InnoDB |
|---|---|---|
| Locking | Table-level locking | Row-level locking |
| Read Performance | Faster for read-heavy workloads | Slower for simple reads due to MVCC overhead |
| Write Performance | Slower in high-concurrency environments | Better for write-intensive workloads with high concurrency |
| Indexing | Native full-text indexing | Support for full-text indexing since MySQL 5.6 |
| Transactions | No support for transactions | Full ACID compliance, supports transactions |
| Foreign Key Support | Does not support foreign keys | Supports foreign key constraints |
| Concurrency | Can experience bottlenecks with table locking | Supports high concurrency with row-level locking |
| Disk Space Usage | Generally uses less disk space but needs manual maintenance | Dynamically manages disk space with auto reclamation |
| Crash Recovery | Limited crash recovery | Automatic crash recovery due to transaction logs |
| Best Use Case | Read-heavy workloads with simple data structures | Applications requiring transactions, concurrency, and reliability |
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.
