What’s the difference between InnoDB and MYISAM?

MYISAM:

MyISAM extends the former ISAM storage engine. The MyISAM tables are optimized for compression an speed. MyISAM tables are also portable between platforms and Oss.. The original storage engine was ISAM, which managed non-transactional tables. This engine has been replaced by MyISAM and should no longer be used. It is deprecated in MySQL 4.1, and is removed in subsequent MySQL release series. MyISAM is an improved replacement for ISAM.

In MySQL 3.23.0, the MyISAM and HEAP storage engines were introduced. The HEAP storage engine provides in-memory tables. The MERGE storage engine was added in MySQL 3.23.25. It enables a collection of identical MyISAM tables to be handled as a single table. All three of these storage engines handle non-transactional tables, and all are included in MySQL by default. Note that the HEAP storage engine has been renamed the MEMORY engine. The size of MyISAM table can be up to 256TB, which is huge.

In addition, MyISAM tables can be compressed into read-only tables to save space. At startup, MySQL checks MyISAM tables for corruption and even repair them in case of errors. The MyISAM tables are not transaction-safe. Before MySQL version 5.5, MyISAM is the default storage engine when you create a table without explicitly specify the storage engine. From version 5.5, MySQL uses InnoDB as the default storage engine.

INNODB:

The InnoDB and BDB storage engines that handle transaction-safe tables were introduced in later versions of MySQL 3.23. Both are available in source distributions as of MySQL 3.23.34a.

BDB is included in MySQL-Max binary distributions on those operating systems that support it. InnoDB also is included in MySQL-Max binary distributions for MySQL 3.23. Beginning with MySQL 4.0, InnoDB is included by default in all MySQL binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like. The InnoDB tables fully support ACID-compliant and transactions. They are also very optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-and forward operations. The size of the InnoDB table can be up to 64TB. Like MyISAM, the InnoDB tables are portable between different platforms and OSs. MySQL also checks and repair InnoDB tables, if necessary, at startup.

 

Differences: 

1. MYISAM supports Table-level Locking where as InnoDB supports Row-level Locking.
2. MyISAM designed for need of speed where as InnoDB designed for maximum performance when processing high volume of data.
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS where as InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS.
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI).
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done where as InnoDB supports transaction. We can commit and rollback with InnoDB.
6. MYISAM supports fulltext where as InnoDB does not support fulltext.
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

8. MyISAM  stores its tables and indexes in a file where as InnoDB stores its tables and indexes in a tablespace.

Posted in MySQL | Tagged , , | 1 Comment

One Response to What’s the difference between InnoDB and MYISAM?

  1. Mysql Guru says:

    Good article!
    Its clears my logic for innoDB and myisam storage engine. Thanks for this useful post.