InnoDB and MyISAM are storage engines for MySQL DBMS. Prior to version 5.5, the latter was used as a default storage engine by MySQL. It is now using InnoDB as a default.
This page focusses on the difference between both these storage engines in MySQL.
- InnoDB supports transactions by tables while MyISAM does not, i.e if there are a bunch of DML operations (SELECT, INSERT, UPDATE, DELETE, etc) done on the database in a single batch, it can be reverted if there is any issue encountered in between.
- InnoDB supports foreign key constraints while MyISAM does not.
- Concurrency - InnoDB locks the particular row in the table. With MyISAM, a DML statement will obtain an exclusive lock on the entire table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.
- Performance speed of MyISAM table is much higher as compared with tables in InnoDB.
- As InnoDB supports row-level locking which means inserting and updating is much faster as compared with MyISAM.
- InnoDB is a better option if you are dealing with the larger database because it supports transactions, volume while MyISAM is suitable for small projects.
- InnoDB does not support FULLTEXT index(until MySQL 5.6) while MyISAM supports.
- InnoDB doesn't remember auto-increment values (it resets them to the highest ids in the tables when MySQL restarts). So if you use InnoDB and you need unique ids, you need to implement a workaround (e.g. keep the latest ids in a separate table). From the docs -
In MySQL 5.7 and earlier, the auto-increment counter is stored only in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB would execute the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. SELECT MAX(ai_col) FROM table_name FOR UPDATE; In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.