MySQL InnoDB tables vs. MyISAM tables


MySQL has multiple ways in which it can store your data, called storage engines. The two most commonly used engines are MyIASM and InnoDB; I have a hard time remembering which I wanted to use, and answering people when they ask why I used the type I did. So…

InnoDB tables support foreign keys; MyISAM does not. (ref)

“Full-text indexes can be used only with MyISAM tables”. (ref)

InnoDB supports transactions! Note autocommit is set by default. (ref)

Because MyISAM tables are non-transaction-safe they may be smaller, faster, and require less memory to update (ref).

The InnoDB engine also supports Clustered indexes, Data caches, and has finer locking granularity than MyISAM, although MyISAM does have something called “Geospatial indexing support.”

The official summaries:

MyISAM — The default MySQL storage engine and the one that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.

InnoDB — A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent non-locking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

No Comments

No comments yet.

Comments RSS TrackBack Identifier URI

Leave a comment


powered by WordPress     themed by Mukkamu     presented by ideaharbor.org     everything else by steve hulet