storage engine

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

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




--