Difference between InnoDB and MyISAM.

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.

InnoDB and MyISAM

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.