Differences between InnoDB and MyISAM

 

mysql_innodb_myisam-minFirst major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn't have FULLTEXT search indexes until v5.6, as does MyISAM. InnoDB also implements transactions, foreign keys and relationship constraints while MyISAM does not.

The list can go a bit further. Yet, they both have their unique advantages in their favor and disadvantages against each other. Each of them is more suitable in some scenarios than the other.

So to summarize (TL;DR):

InnoDB has row-level locking, MyISAM can only do full table-level locking.
InnoDB has better crash recovery.
MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.

MYISAM:

  • MYISAM supports Table-level Locking
  • MyISAM designed for need of speed
  • MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
  • MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
  • MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
  • MYISAM supports fulltext search
    You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

  • InnoDB supports Row-level Locking
  • InnoDB designed for maximum performance when processing high volume of data
  • InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
  • InnoDB stores its tables and indexes in a tablespace
  • InnoDB supports transaction. You can commit and rollback with InnoDB

Facebook Comments

Post a Comment

Your email address will not be published. Required fields are marked *

© Franz East 2009 - 2016. | Powered by Coffee + Cigarete & Site Hosted By IDwebhost.