InnoDB-Vs-MyISAM

12 Difference Between INNODB And MyISAM

by

InnoDB is a storage engine for the database management system MySQL. It provides the standard ACID-Compliant transaction features, along with foreign key support (Declarative Referential Integrity). InnoDB supports:

  • Tablespaces
  • Foreign Keys
  • Full text search indexes
  • Spatial operations
  • Virtual columns
  • Both SQL and XA transactions.

MyISAM is the default storage engine for the MySQL relational database management system versions prior to 5.5 released in December 2009. It is based on the older ISAM code, but it has many useful extensions.

MyISAM:

  • Does not support foreign keys
  • Index statistics are usually quite accurate
  • Allows fast reads due to structure of its indexes.
  • It involves queries on very big tables
  • Supports full text search indexes

The Difference: InnoDB VS MyISAM

  1. InnoDB is the default storage engine for MySQL 5.5 and higher whereas MyISAM is the default storage engine for MySQL 5.4 and lower.
  2. InnoDB supports row-level locking while MyISAM supports table-level locking.
  3. InnoDB stores its tables and indexes in tablespace while MyISAM stores tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
  4. InnoDB is a relational DBMS (RDBMS) and therefore it has Referential Integrity and supports Foreign Key, including cascade deletes and updates whereas MyISAM is not an RDBMS and hence does not support Referential Integrity and Foreign Key.
  5. InnoDB is faster for writes whereas MyISAM is faster for reads.
  6. InnoDB supports large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  7. In InnoDB there is No Full Text Search whereas Full Text Search is supported in MyISAM.
  8. InnoDB supports ACID (Atomicity, Consistency, Isolation and Durability) properties. On the other hand, MyISAM does not support ACID (Atomicity, Consistency, Isolation and Durability) properties.
  9. InnoDB supports Transactions (Rollback, Commit) whereas MyISAM does not support Transactions.
  10. MyISAM is not dynamic in nature, all future enhancements will be in InnoDB due to its dynamic nature, for example with the roll out of version 8.0, its clear that, all future enhancements will be on InnoDB.
  11. InnoDB’s performance for high volume data is by far better than that of MyISAM.
  12. InnoDB gives reliability as it uses a transactional log to maintain such operations and hence, in case of failure, it can recover easily by using those logs. On the other hand, MyISAM offers no data integrity; hardware failures and canceled operations can cause data to become corrupt.

Also Read: Difference Between DBMS And RDBMS

INNODB Vs. MyISAM In Tabular Form

BASIS FOR COMPARISON InnoDB MyISAM
Type Of MySQL Version Stored InnoDB is the default storage engine for MySQL 5.5 and higher.   MyISAM is the default storage engine for MySQL 5.4 and lower.
Table-Locking Vs Row-Locking InnoDB supports row-level locking.   MyISAM supports table-level locking.  
Storage Of Tables, Data And Indexes InnoDB stores its tables and indexes in tablespace. MyISAM stores tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)  
Referential Integrity And Foreign Key InnoDB is a relational DBMS (RDBMS) and therefore it has Referential Integrity and supports Foreign Key, including cascade deletes and updates. MyISAM is not an RDBMS and hence does not support Referential Integrity and Foreign Key.  
Speed InnoDB is faster for writes.   MyISAM is faster for reads.    
Caching InnoDB supports large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.  
Full Text Indexing In InnoDB there is No Full Text Search.   Full Text Search is supported in MyISAM.    
Acid Properties InnoDB supports ACID (Atomicity, Consistency, Isolation and Durability) properties. MyISAM does not support ACID (Atomicity, Consistency, Isolation and Durability) properties.  
Transactions InnoDB supports Transactions (Rollback, Commit).   MyISAM does not support Transactions.  
Nature With the roll out of version 8.0, it’s clear that, all future enhancements will be on InnoDB.   MyISAM is not dynamic in nature.
Performance InnoDB’s performance for high volume data is by far better than that of MyISAM.   MyISAM performance for high volume data is poor than that of InnoDB.  
Reliability InnoDB gives reliability as it uses a transactional log to maintain such operations and hence, in case of failure, it can recover easily by using those logs.   MyISAM offers no data integrity; hardware failures and canceled operations can cause data to become corrupt.

Also Read: Difference Between OORDBMS And ODBMS