Mind Dump, Tech And Life Blog
written by Ivan Alenko
published under license Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)copy! share!
posted at 21. Apr '18

MySQL storage engines InnoDB vs. MyISAM

TL;DR use InnoDB, because it has transactions and better integrity check, but worse fulltext search (<= 5.6) (who cares?). For the best fulltext search consider using Elasticsearch or Apache Solr, but they are additional daemons.

A storage engine is an internal representation of data in a database and has a interface for working with the database. And some has more features like triggers or constraints. I think there is also a JSON storage in MariaDB and PostgreSQL.

I copied this from somebody on http://stackoverflow.com/questions/15678406/when-to-use-myisam-and-innodb.

MyISAM:

  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  • Full-text indexing.
  • Especially good for read-intensive (select) tables.

InnoDB:

  • Support for transactions (giving you support for the ACID property).
  • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
  • Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
  • InnoDB is more resistant to table corruption than MyISAM.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future enhancements will be in InnoDB.

MyISAM Limitations:

  • No foreign keys and cascading deletes/updates
  • No transactional integrity (ACID compliance)
  • No rollback abilities
  • Row limit of 4,284,867,296 rows (232)
  • Maximum of 64 indexes per row

InnoDB Limitations:

  • No full text indexing (Below-5.6 mysql version)
  • Cannot be compressed for fast, read-only

Add Comment