MySQL Engines

Webbing Wednesdays Week #4

MySQL storage engine is what stores, handles and retrieves information from a table. Truth is there is nothing like a perfect storage engine or rather a recommended storage engine. However most applications run the default MySQL Engine as MyISAM. Following are some most frequently used engines.

MyISAM

MyISAM is one of the most renowned and highly used default storage engine. Just to further define untill version 5.5 MyISAM was the default storage engine. It is used where large amount of data is to be processed with no requirement of non-transactional operations. It’s operations are based on Table Level Locking.

MyISAM was designed to read the queries at the much faster speed. However the engine doesn’t support the writing of the loads very well. One of the major flaws that the engine has is; it isn’t crash safe. MyISAM tables have a read queue and a write queue. Queries are placed into one of those two queues to be processed. The write queue has higher priority than the read queue, but the table can only process one write query at a time. Multiple read queries can occur at once, so the read queue will often be empty. Each query has to wait its turn to run to be executed. It does not support Foreign key constraints. Because of absence of transaction log; it doesn’t support rollback options. It supports full text indexing.

InnoDB

It is designed for maximum performance when processing large data volumes. It is a default engine after MySQL version 5.5. The major difference between InnoDB and MyISAM lies in transactional operation and foreign key constraints support. All the tables are arranged on disk to optimize queries based on primary keys. It has row-level locking (which helps increase the time performance). Because of row-level locking it allows parallel query to be run on same table; which in turn requires higher amount of memory. To maintain data integrity, InnoDB also supports FOREIGN KEY constraints. All Inserts, updates, and deletes are all checked to ensure they do not result in inconsistencies across different tables.

MERGE

The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. By “Identical”; it means all the tables have identical columns and index information. One cannot merge MyISAM tables; where the columns listed have different order in which the columns are listed in a different order. Needless to say that if columns are not same or indexed in different order, they cant merge either. One can use SELECT, DELETE, UPDATE, and INSERT on MERGE tables; however in order to use SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table is a must. Again, MERGE MySQL engine has Table Level Locking.

MEMORY

MEMORY is often termed as HEAP engine. The engine creates the tables with contents and stores them in memory. Default use of hash indexes makes them very fast and useful for creating temporary tables. However, it completely relies on operation of the server and the moment the server needs to be restarted or shut down, all rows stored in MEMORY tables are lost. Question is, do the tables get lost too? Well, not really! The tables continue to exist in .frm files on disk as definition, however the content of the table goes empty with the server reboot. MEMORY tables cannot contain BLOB or TEXT columns. It has Table Level Locking.

EXAMPLE

EXAMPLE is a “Stub” engine, added in MYSQL 4.3.1 and really does nothing. The purpose of this engine is to serve as an example to begin writing new storage engines in the MySQL source code. This engine has been focused primarily on the developers. When you create an EXAMPLE table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. No other files are created. The engine doesn’t store any data into the tables and returns empty results upon retrieval.

ARCHIVE

The ARCHIVE storage engine is primarily used for storing larger amounts of data without indexes. It only supports insert and select query, but doesn’t support delete, update and replace query. Upon an ARCHIVE table creation, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates other files, all having names beginning with the table name. The data and metadata files have extensions of .ARZ and .ARM, respectively. An .ARN file may appear during optimization operations and stores data in compressed format to save disk space. It compresses a record when it is inserted and decompresses it using zlib library as it is read. It does not support indexing.

CSV

CSV storage engine stores data in text file with comma-separated values file format, added in MySQL 4.1.4. Due to lack of security in text format storage, it is not a good idea to use it for large data storage. The storage engine does not support indexing.

BLACKHOLE

The BLACKHOLE storage engine is useful in a replicated environment and it acts as a “black hole”. The storage engine accepts data but throws it away and does not store it. Retrievals always return an empty result. Everytime any user creates a BLACKHOLE table, the server creates a table format file in the database directory. The file begins with the table name which has an .frm extension and no other files associated with the table. It is useful in a replicated environment where all SQL statements are run on all nodes, but want some nodes to actually store the result.