CODEWITHSUNDEEP
mysqldatabase-designinnodbmyisam
openfrog
openfrog

Reputation: 40765

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?

Upvotes: 20

Views: 5408

Answers (6)

Atli
Atli

Reputation: 7930

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:

Upvotes: 12

blockhead
blockhead

Reputation: 9705

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425813

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.

Upvotes: 35

John Parker
John Parker

Reputation: 54445

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

Upvotes: 1

Darin Dimitrov
Darin Dimitrov

Reputation: 1039438

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

Upvotes: 4

Bozho
Bozho

Reputation: 597382

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)

Upvotes: 9

Related Questions