Selecting the Right Mysql Engine

Feb 21 2014

When You designing MySQL based applications, You should decide which engine to use for storing your data. If you don’t think about it during the design phase, you are likely to face complications later in the process.

MyISAM, InnoDB, BDB and HEAP are the most common and popular storage engines. All have some advantages and disadvantages. HEAP is an in-memory storage engine,so we will primarily focus on MyISAM, InnoDB and BDB storage engines.

  • Transactions and Concurrency:- If your application requires transactions and high read/write concurrency, InnoDB is probably your best bet. If your application only need moderate read/write concurrency then BDB might also work for you.
  • If your application depends upon accurate and fast row counts then you can use MyISAM. InnoDB have to count all the rows but MyISAM always knows exact count without re-counting.
  • Only MyISAM tables provide full text functionality.
  • If your tables primarily used for read operations then MyISAM is the most suitable choice. It works most efficiently in that scenario.
  • Locking in MyISAM tables is performed at the table level but InnoDB uses row-level locking in conjunction with a multi-versioning scheme. BDB tables use page level locaking on 8 KB pages. InnoDB uses row-level locking so it gives better performance for high number of insert/update queries. However if your table is generally used for read(select queries) then MyISAM works great as InnoDB’s row-level locking adds some extra overhead.
  • Non-MyISAM tables will generally use more CPU and disk space.
  • MyISAM tables may take a long time to check and repair while InnoDB tables generally recover quickly.
  • Only InnoDB provides foreign key constraints other storage engines do not provide this functionlity.
  • BLOB and TEXT columns in MyISAM table can be indexed. MyISAM tables have a limit of 500 bytes on each key, so the index uses only the first few hundred byte of BLOB or TEXT field. InnoDB also provides lightning fast results for queries that use primary key(s) for look-up.