Mysql: Should I always prefer MySQL InnoDB over MyISAM?

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?

 Answers

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 WHEREcondition. 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.

---

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)

---

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