Storage engines are used to read and write data into a database. It is a very crucial design decision which you need to make when designing a database for an application(especially for a data-intensive application).
MySQL supports several data storage engines, but MyISAM and InnoDB are the most widely used storage engines. Each one of them has its advantages and disadvantages which play an important role in the performance of your application.
In this post, we will be discussing the difference between MyISAM and InnoDB storage engines so you can decide the best-suited engine for your demand and how can you change from one type of engine to another.
“For MySQL version earlier than 5.5.5 MyISAM was default storage engine but InnoDB is the default storage engine for version 5.5.5 and later”
Referential integrity ensures that relationships between tables remain consistent. Or in more general terms a table has a foreign key pointing to a different table. When a change is made to the pointed-to-table then the changes will be restricted/cascaded to the linking table as well.
InnoDB is a relational DBMS (RDBMS) and thus has referential integrity, while MyISAM does not. So InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates, but MyISAM does not support foreign key constraints.
So when you are designing a MySQL database and need to define integrity constraints between tables, InnoDB is your cup of coffee. (Of course, you can have MyISAM engine for the tables you don’t need to define any referential integrity constraints).
Data in a table is managed using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE and DELETE. A transaction group two or more DML statements together into a single unit of work, so either the entire unit is applied, or none of it is.
MyISAM does not support transactions whereas InnoDB does.
So if a table is using the MyISAM engine and operation is interrupted, the operation is aborted immediately, and the rows (or even data within each row) that are affected remain affected, even if the operation did not go to completion. If a table is using the InnoDB engine and operation is interrupted, because it is using transactions, which has atomicity, any transaction which did not go to completion will not take effect since no commit is made.
When you run an operation in MyISAM, the changes are set and you cannot roll back the changes while in InnoDB, those changes can be rolled back.
When you are working on an application that uses a lot of transactions, InnoDB would be a better option than MyISAM.
InnoDB also provides auto-recovery after a crash of the MySQL server or the host on which the server runs.
When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.
When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for the other operations. This means queries can run simultaneously on the same table, provided they do not use the same row.
MyISAM offers no data integrity – Hardware failures, unclean shutdowns, and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.
InnoDB, on the other hand, uses a transactional log, a double-write buffer, and automatic checksum and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would auto-recover through the replay of those logs.
InnoDB does not support full-text indexing until MySQL version 5.6.4. Although it is a very old version but, some applications still using the same or below versions of MySQL(especially shared hosting providers). However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL.
A MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.
InnoDB requires a lot of memory (buffer pool). The data and indexes are cached in memory. Changes are written to the log buffer (physical memory) and are flushed every second to the log files (method depends on innodb_flush_log_at_trx_commit value). Having the data in memory is a huge performance boost. MyISAM only caches indexes (key_buffer_size) so that’s where you would allocate most of your memory if you’re only using MyISAM.
InnoDB supports ACID(Atomicity, Consistency, Isolation, and Durability) while MyISAM does not support the ACID property.
I am the owner of acmeextension. I am a passionate writter and reader. I like writting technical stuff and simplifying complex stuff.