Tag Archives: storage engine

04 Jan

Difference MyISAM and InnoDB Storage Engines Mysql

MyISAM

InnoDB

Default since version 3.23

Relatively newer

Files storage:

FRM: table definition,

MYD: Actual Data

MYI: Index File

FRM: table definition

.ibd file (concept of table space) or single huge .ibdataX

No transation, Foreign key

ACID Transactions, Foreign key, Rollback

Low disk and memory utilisation

Relatively high storage requirements (Almost 3 times disk space)

Non clustered indexing

Clustered indexing for Primary key

Table level locking

Row level locking

Fulltext Indexes, Merge tables, Compressed tables.

Not available

GIS, RTREE indexes

Not available

Not avalable

HASH lookups

01 Jan

Choosing between MyISAM and INNODB – MySQL Storage Engines

After reading at a lot of places for the the single repeatative question, “What engine shall I choose – MyISAM or Innodb?”, this is what I’ve got.

Following are points of consideration for MyISAM – MySQL storage engine:

  • Tables are really fast for select-heavy loads
  • Table level locks limit their scalability for write intensive multi-user environments.
  • Smallest disk space consumption
  • Fulltext index
  • Merged and compressed tables.

Following are points of consideration for InnoDB – MySQL storage engine:

  • ACID transactions
  • Row level locking
  • Consistent reads – allows you to reach excellent read write concurrency.
  • Primary key clustering – gives excellent performance in some cases.
  • Foreign key support.
  • Both index and data pages can be cached.
  • Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still recover to the consistent state- No check / repair like MyISAM may require.
  • All updates have to pass through transactional engine in InnoDB, which often decreases performance compared to non-transactional storage engines.

Also consider:

  • Choose MyISAM for large constant tables or logging tables, relatively infrequent updates or Fast selects – these will not lock the table for the long time and thus it will not reduce performance.
  • Choose MyISAM if you really don’t need InnoDB.
  • Choose InnoDB storage engine when following is required:
  • Intensively updated tables – which can have many long selects running at the same time.
  • Multi-statement transactions Advanced isolation levels and row-level locking Foreign key constraints. Well we can say for regular usage: MyISAM is for speed and InnoDB for data integrity.
-- Kedar Vaijanapurkar --