What is the differences between InnoDB and MyISAM?


InnoDB

MyISAM

InnoDB implements row-level locking. MyISAM implements table-level locking.
Automatic Crash Recovery. No Crash Recovery
InnoDB doesn’t have FULLTEXT search indexes. MyISAM have FULLTEXT search indexes.
InnoDB also implements transactions, foreign keys and relationship constraints. MyISAM does not support transactions, foreign keys and relationship constraints.
InnoDB stores both data and indexes in one file. MyISAM stores indexes in one file and data in another.
InnoDB uses a buffer pool to cache both data and indexes. MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.

In InnoDB the COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) execute slower than in MyISAM because the row count is not stored internally.

Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.