database engine and database table types in mysql-mysqli

Engines or Engine types are storage handlers for database tables. MySQL / MySQLi supports different engines like MyIASM, InnoDB, HEAP (Memory), CSV, ARCHIVE, BLACKHOLE, MERGE (MRG_MyISAM) etc.

Engine selection is depends upon what kind of requirement you have. Generally, people do avoid it as they don't have much knowledge about it. But engine is performing very important role in your transaction and table data safety. Most people uses MyIASM and InnoDB. InnoDB is having higher advantage and very safe engine compare to MyIASM. We will see its comparison below.

MyIASM

MyIASM is default engine in MySQL/MySQLi. It used very oftenly and oldest engine database. It is suitable for simple and small application where there is no importance of security and relational database. MyIASM is unable to setup relational key(Foreign keys). 

It provides high-speed storage and retrieval, as well as fulltext searching capabilities. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default. It has very average rating for table design. 

MyIASM is extension of IASM which is not available at all. 

Each MyIASM table stored on disk with three files. The files name are same as table name and have three different file types. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

InnoDB

InnoDB is one of the good and safe engine for transaction. It is transaction safe engine and follows ACID property(Atomicity, Consistency, Isolation, Durability). It has commit, rollback, and crash-recovery capabilities to protect user data.  

InnoDB row-level locking and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDBstores 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. You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. Simply we can say that  nnoDB is very reliable due to transactional nature of data operations, and makes it very good choice for systems where backup operation are essential and often needed. InnoDB tables are reliable and has many safety measures implemented to make sure that data remains safe. 

InnoDB is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data in InnoDB, and another site handles an average load of 800 inserts/updates per second in InnoDB.  

It has only one file type .frm, which means reduce in other extra files compare to MyIASM. Possibly prefer InnoDB over MyIASM. 

MEMORY (HEAP)

The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term. 

As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and makes it very useful for creating temporary tables.  

As its being volatile, when the server shut down all the data are lost only table definition remains(becomes empty table), means needs to store the data before shutting down the server.  

As table definition remains in database, it creates one file of type .frm extension.

MERGE (MRG_MyISAM)

The MERGE storage engine is also known as MRG_MyISAM. It is collection of identical MyIASM tables, which cab be used as one. 

“Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order. 

When you create a MERGE table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format, and an .MRG file contains the names of the underlying MyISAM tables that should be used as one. The tables do not have to be in the same database as the MERGE table. 

To create a MERGE table, you must specify a UNION=(list-of-tables) option that indicates which MyISAMtables to use.

CSV

The CSV storage engine stores data in text files using comma-separated values format. It can be used on windows only in newer version which is higher than MySQL 5.1. 

When you create a CSV table, 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 a data file. Its name is same as table name and has a .CSV extension. The data file is plain text file. When you store data into the table, the storage engine saves it into the data file in comma-separated values format (CSV).

ARCHIEVE

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint. 

Archive storage engine does compresses your data and should be good for log storage or storing something which needs no updates and no indexes, however compression ratio is not perfect, configuration and stats could be improved.  

The ARCHIVE engine supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It does support ORDER BY operations, BLOB columns, and basically all but spatial data types. The ARCHIVE engine uses row-level locking.  

When you create an ARCHIVE table, 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 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.

BLACKHOLE

The BLACKHOLE storage engine act as a "black hole", it accepts data and throws it away and does not store anything. Retrieves only empty result. 

When you create a BLACKHOLE table, only the .frm file is created, because it does not reuire to store data so no .MYD and .MYI file needed.  

If you run in a high volume production system where you may have one or more master databases for writes/updates/deletes and a whole bunch of slaves reading the log from that master which in short increase the network traffic, disk I/O, CPU power and others, this affect the performance of a Master Server.

What you then have is the Master only replicating to one database, increasing the Master’s capacity to process transactions. The slaves consume the log file from the Blackhole server. It acts as a proxy at this point, with the benefit of freeing up resources on the Master’s server. 

The master writes to its binary log. The “Blackhole” server acts as a proxy slave server, it does not actually store any data, so there is little processing overhead incurred and other SLAVE server will read a binary log from this server. 

Other possible uses for the BLACKHOLE storage engine include:

- Verification of dump file syntax.

- Measurement of the overhead from binary logging, by comparing performance using  BLACKHOLE with and without binary logging enabled.