What’s the difference between MyISAM and InnoDB?

Posted: June 7, 2017 in All Category, Database

InnoDB stores data in a file(s) called a tablespace. In newer versions of MySQL, you can store the data and indexes in separate files. MyISAM stores the data and indexes in two files (.MYD, .MYI). InnoDB is extremely critical about its tablespace and log files (They normally should be backed up together). You can’t just backup your data by copying files like you would with MyISAM. In some situations you can only restore a table to the server from which you backed it up!

InnoDB are built on clustered indexes and uses MVCC to achieve high concurrency. This provides very fast primary key lookups. MyISAM doesn’t support transactions, FK contraints, or row-level locks. MyISAM uses shared and exclusive locks on the entire table. However, concurrent reads & inserts for new rows are allowed.

InnoDB is crash safe (Assuming your flushes are truly durable on disk and not on some volatile cache). MyISAM is no where close to being crash safe. If you care about your data, use InnoDB. It might be OK to use MyISAM for read-only workloads.

MYISAM

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
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.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB
6. InnoDB has better crash recovery.

Advertisements

Comments are closed.