MySQL STORAGE ENGINES

What is storage engine?

       In MySQL the datas are stored as files in any one of the types in storage engines. MySQL supports pluggable storage engines that we can use any types of engine belongs to your data.There are two types of storage engines in MySQL  Transactional(The data can be modified in engines) and non-transactional(It can only fetch the data from engines). The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

Types of Storage Engine

1.MyISAM

2.InnoDB

3.Merge

4.Memory

5.Blackhole

6.Archive

7.CSV

8.Federated

1.MyISAM

       MyISAM is the oldest storage engines and it does not support transactions.It provides table level locking.If you create a table without representing the storage engine it take MyISAM engine as default (before MySQL 5.5 version ).MySQL databases are generally store in data directories and MYISAM tables are stored using 3 files.

          .frm - Table structure

          .MYD - Data file

          .MYI - Index file

MyISAM has most flexible auto increment.The tables can be used to set up merge tables.The table storage format is portable.Maximum no of indexes per table = 64.Maximum no of columns per index = 16.Blob and TEXT columns can be indexed.Table size is 256TB. It does not provide for foreign_key.

2.InnoDB

       InnoDB is a Transactional safe storage engine.It is an ACID (Atomicity, Consistency, Isolation, Durability) compliant storage engine.It has commits,rollback and crash recovery capabilty to recover the data.InnoDB provide row level locking to use multi user concurrency and performance.It supports foreign key. InnoDB creates two log files namely "ib_logfile0" and "ib_logfile1" and a data file "ibdata1" in the MySQL data directory where it stores its tables.MySQL creates an auto-extending 10MB data file in ibdata1 and two 5MB log files in ib_logfile0 and ib_logfile1 in the MySQL data directory. The table definitions are stored in database directory with a .frm extension whereas the data is stored in the "ibdata1" - tablespace.Minimum tablespace size is 10MB. And maximum tablespace size is 64TB.

3.Merge

       The MERGE engine type allows you to combine a number of identical tables into a single table. You can then execute queries that return the results from multiple tables as if they were just one table. Each table merged must have the same table definition. The MERGE table is particularly effective if you are logging data directly or indirectly into a MySQL database and create an individual table per day, week or month and want to be able to produce aggregate queries from multiple tables. Transaction No Locking level Table

4.Memory

       The MEMORY storage engine (previously known as the HEAP storage engine) stores all data in memory; once the MySQL server has been shut down any information stored in a MEMORY database will have been lost. However, the format of the individual tables is kept and this enables you to create temporary tables that can be used to store information for quick access without having to recreate the tables each time the database server is started.Cannot contain BLOB or TEXT columns.It has table level locking .

5.Blackhole

        It acts as a “black hole” that accepts data but throws it away and does not store it. BLACKHOLE engine does not actually store any data. Although you can create tables and indexes, all SQL statements that would add or update information to the database are executed without actually writing any data. The database structure is retained, however, and you can create any indexes on the (non-existent) information that you want.

6.Archive

       It is used for storing large amounts of data without indexes in a very small footprint.It supports only the INSERT and SELECT statements, but does support most of the MySQL field types. Information stored in an ARCHIVE storage engine table is compressed and cannot be modified and so ARCHIVE tables are perfect for storing log data When an archive table is created, following files are created in the database directory.

           .frm - table definition
           .ARZ - DATA file
           .ARM - METADATA file

       It does NOT support DELETE, REPLACE and UPDATE.It provides row level locking.

7. CSV(Comma Separated Value)

       It stores data in text files using comma-separated values format. When a table is created 2 files are created in the database directory

       .frm - table definition
       .CSV - data file

It is not an efficient method for storing large volumes of data, or larger data types like BLOB, although such types are supported. There is also no indexing. However, because the data is stored in the CSV format it is exceedingly portable.

8.Federated

     The FEDERATED storage engine (added in MySQL 5.03) enables you to access data from remote MySQL database (other databases may be supported in the future) as if it were a local database. In effect, the MySQL server acts as a proxy to the remote server, using the MySQL client access library to connect to the remote host, execute queries and then reformat the data into the localized format.It does not support transactions.

Features of  MySQL Storage Engines