mySQL  Storage Engines

what is a storage engine?

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

Each of these different techniques and suites of functionality within the MySQL system is referred to as a storage engine (also known as a table type).

mysql provides various storage engines for its tables as below:

  • myisam
  • innodb
  • merge
  • memory(heap)
  • example
  • archive
  • csv
  • blackhole

syntax:

          create     table       table_name  (

                                                             variable_name     data_type(size),
              
                                                             variable_name     data_type(size),
              
                                                             ....
                  
                                                          ) engine  =  storage_engine_type;

or

          create     table       table_name  (

                                                             variable_name     data_type(size),
              
                                                             variable_name     data_type(size),
              
                                                             ....
                  
                                                          ) type  =  storage_engine_type;

myisam

the myisam tables are optimized for compression an speed.myisam tables are also portable platforms and oses.

the size of myisam table can be up to 256tb.the myisam tables are not transaction-safe.

All numeric key values are stored with the high byte first to permit better index compression. NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.

example:

          create table    data(
    
                                        firstname      varchar(10),
                        
                                        lastname       varchar(20)
                        
                                        ) engine = myisam;

innodb

it is the most widely used storage engine with transaction support.it is an acid compliant storage engine.it supports row-level locking, crash recovery and multi-version concurrency control.it is the only engine which provides foreign key referential integrity constraint.

the innodb storage engine maintains its own buffer pool for caching data and indexes in main memory.the size of the innodb table can be up to 64tb.

example:

          create table    data(
    
                                        firstname      varchar(10),
                        
                                        lastname       varchar(20)
                        
                                        ) engine = innodb;

merge

A MERGE table is a virtual table that combines multiple MyISAM tables, which has similar structure, into one table. The MERGE storage engine is also known as the MRG_MyISAM engine.The MERGE table does not have its own indexes; it uses indexes of the component tables instead.

using merge table, you can speed up performance in joining multiple tables.

example:

          create table    data(
    
                                        firstname      varchar(10),
                        
                                        lastname       varchar(20)
                        
                                        ) engine = merge;

memory

In the MySQL - Memory storage engine creates tables in memory.It is the fastest engine. It provides table-level locking. It does not support transactions.Memory storage engine is ideal for creating temporary tables or quick lookups.The data is lost when the database is restarted.


csv

CSV stores data in csv files. It provides great flexibility, because data in this format is easily integrated into other applications.

CSV table does not support NULL data type and read operation requires a full table scan


archive

Archive storage engine is optimized for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing, retrieving large amounts of seldom referenced historical, archived data.

In MySQL Database archive type engine tables only allow INSERT and SELECT commands. The archive tables do not support indexes, so reading records requires a full table scanning.

example:

          create table    data(
    
                                        firstname      varchar(10),
                        
                                        lastname       varchar(20)
                        
                                        ) engine = archive;

federated

Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.

The local federated table stores no data. When you query data from a local federated table, the data is pull automatically from the remote federated tables.

example:

          create table    data(
    
                                        firstname      varchar(10),
                        
                                        lastname       varchar(20)
                        
                                        ) engine = federated;