Database Interview QUestion: MySQL Storage Engines

MySQL supports several different types of Table Engines also known as "Table Types". A database can have its tables being a mix of different table engine types or all of the same type

Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default and most general-purpose storage engine.

To determine which storage engines your server supports, use the SHOW ENGINES statement.

MyISAM vs Innodb - Quick comparison Table:

MyISAM	Innodb
Not *ACID compliant and non-transactional	*ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys
Default Engine Prior to 5.5	Default Engine Now
Requires full repair/rebuild of indexes/tables	Auto recovery from crash via replay of logs
Changed Db pages written to disk instantly	Dirty pages converted from random to sequential before commit and flush to disk
No ordering in storage of data	Row data stored in pages in PK order
Table level locking	Row level locking

Oracle recommends using InnoDB for tables except for specialized use cases.
InnoDB is under active development, MyISAM is not.

INNODB support FOREIGN-KEY referential-integrity constraints.MYISAM didn't support.

http://www.rackspace.com/knowledge_center/article/mysql-engines-myisam-vs-innodb

MyISAM is optimized for environments with heavy read operations, and few writes, or none at all.
MyISAM is best suited for high “select” query rate, and non transactional operations.
InnoDB is best suited for parallel insert/update/delete operations (because of row level locking), and transactional operations (because of roll back feature).
Memory engine (HEAP) is best suited for on the fly fast data access, as everything is stored in the RAM.

Disadvantages of MyISAM
No data integrity (e.g. relationship constraints) check.
Doesn’t support transactions which is essential in critical data applications such as that of banking.
Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.


MyISAM uses table-level locking on updates and deletes to any existing row, with an option to append new rows instead of taking a lock and inserting them into free space. InnoDB uses row-level locking.

InnoDB automatically groups together multiple concurrent inserts and flushes them to disk at the same time.
MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches.


Both InnoDB and MyISAM support full-text search, with InnoDB gaining full-text index support in MySQL 5.6.4.

CREATE TABLE lookup
  (id INT, INDEX USING BTREE (id))
  ENGINE = MEMORY;

How to change your table Engine type from MyISAM to Innodb?

ALTER TABLE <table-name> ENGINE=INNODB;

From http://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

MySQL 5.7 Supported storage Engines

• InnoDB: The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and erformance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
• MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
• Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTERprovides fast key-value lookups for huge distributed data sets.
• CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
• Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
• Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.
• Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.
• Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
• Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved.

References

http://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

http://www.rackspace.com/knowledge_center/article/mysql-engines-myisam-vs-innodb