MySQL Engine

Mysql storage engine is the underlying software component. Mysql uses engines to create, read, update and delete (CRUD) data from a database.As of MySQL 5.5 and later InnoDB is the default engine.

Mysql Engine Types

  • It Does not support transactional TRANSACTION.
  • Provides high-speed storage and retrieval, supports full text searching.
  • It is Child, of the ISAM engine.
  • The MyISAM type is great for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate.
  • MyISAM table can be up to 256TB.
  • TEXT and BLOB columns can be indexed.
  • MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory.
  • Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas.
  • MEMORY tables cannot contain TEXT or BLOB columns.
  • MEMORY tables support AUTO_INCREMENT columns.
  • MEMORY tables are stored in memory and used hash indexes so that they are faster than MyISAM tables.
  • MERGE storage engine, also known as the MRG_MyISAM engine.
  • MERGE storage engine was added in MySQL 3.23.25.
  • MERGE Perform more efficient repairs. It is easier to repair individual smaller tables that are mapped to a MERGE table than to repair a single large table. .
  • You can speed up performance in joining multiple tables by MERGE table.
  • An EXPLAIN statement to show how the optimizer resolves a query.
  • InnoDB is the most widely used storage engine because of its transaction support feature.
  • InnoDB supports row-level locking, crash recovery and multi-version concurrency control.Only InnoDB provides foreign key referential integrity constraint.
  • InnoDB tables are free to mix with tables of other MySQL storage engine.
  • InnoDB automatically groups together multiple concurrent inserts and flushes them to disk at the same time.
  • Both InnoDB support full-text search.
  • InnoDB table can be up to 64TB.
  • CSV engine was added in MySQL 4.1.4.
  • CSV storage engine stores data in text files using comma-separated values format.
  • It provides great flexibility to user, because data in this format is easily integrated into other applications.
  • Tables created in CSV engine does NOT support indexing.
  • Table created in CSV engine does not support NULL data type .
  • ARCHIVE storage engine was added in MySQL 4.1.3 as well.
  • Archive storage engine support high speed inserting.
  • ARCHIVE does not support transactions.
  • ARCHIVE engine uses row-level locking.
  • ARCHIVE storage engine compresses a record when it is inserted and decompress it using zlib library as it is read.
  • BLACKHOLE storage engine can be used to perform performance tests or other testing.
  • BLACKHOLE storage engine supports all kinds of indexes.
  • FEDERATED storage engine is available beginning with MySQL 5.0.3. .
  • FEDERATED storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. No data is stored on the local (federated) tables because queries on the local server are automatically executed on the remote (federated) tables.