Should I always prefer MySQL InnoDB over MyISAM?

Question

Someone just told me that InnoDB is much better than MyISAM. So when I create a table, should I always try to use InnoDB Engine instead of MyISAM? Or do both have it's big benefits?

Answer 1

MyISAM is transactionless and heap-organized. The records are identified by the row offset in the table and the indexes store this offset as a row pointer.

InnoDB supports transactions and is index-organized. The records are identified by the value of the PRIMARY KEY (or a hidden internal column is there is no PRIMARY KEY defined) and are stored in a B-Tree. The secondary indexes store the value of the PRIMARY KEY as a row pointer.

Queries that involve full table scans or secondary index lookups are usually faster on MyISAM tables.

Queries that involve PRIMARY KEY seeks are usually faster on InnoDB tables.

MyISAM tables store the number of records in the table in the table's metadata, that's why the queries like this:

SELECT  COUNT(*)
FROM    myisamtable

are instant.

MyISAM tables are completely locked on the DML operations (with several exceptions).

InnoDB tables lock individual records and index gaps, however these are the records and the gaps that are scanned, not only those matched by the WHERE condition. This can lead to the records being locked despite the fact they don't match.

InnoDB tables support referential integrity (FOREIGN KEYs) . MyISAM tables don't.

There are several scenarios that can show benefits of both engines.

Answer 2

To put it simply:

You should use InnoDB:

• if you need transaction support
• if you need foreign keys

You should use MyISAM:

• if you don't need the above AND
• you need speed (faster database operations)

Answer 3

IMHO you should always prefer InnoDB over MyISAM because of the transactional support which is at the heart of every relational database system.

Answer 4

InnoDB is a fully ACID compliant database engine and therefore offers support for transactions, etc. As such, it can therefore be slower than the MyISAM database which tends to be optimised in a different direction.

Therefore if you need transactions InnoDB (or another RDBMS such as PostgreSQL) is the obvious choice.

There's a reasonable comparison over on Wikipedia

Answer 5

You could use both. You could use InnoDB for your write database/transactions, but read back from an MyISAM database, which is denormalized. This gives the best of both worlds. You know you're data is safe with referential integrity from InnoDB, and you can read it as fast as you'd like with MyISAM.

Answer 6

Simple answer: No, you shouldn't.

A few points about each engine:

InnoDB

• Full support for Foreign Key restraints and transactions. (ACID compliant)
• Row-level locking.
• Arguably faster INSERT statements. (And possibly UPDATE as well)

MyISAM

• Faster SELECT statements.
• Support for Full-Text searches
• BLOB and TEXT columns can be indexed
• 4x max storage limit, compared to InnoDB (256TB vs 64TB)

See more in the Manual:

• MyISAM
• InnoDB