MySQL Storage engines

A storage engine(Data Base Engine) is a software which a Data Base management System uses to create, read, update and delete (CRUD) data from a database.

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. Choosing the right storage engine is an important strategic
decision, which will impact future development.

mysql> show variables like "default_storage%";

+------------------------+--------+

| Variable_name | Value |

+------------------------+--------+

| default_storage_engine | InnoDB |

+------------------------+--------+

mysql> SET default_storage_engine=”myisam”;

Types of mysql storage engines

1) Transactional storage engines  : Innodb,Nbd
2) Non-Transactional storage engines : MyISAM,MEMORY,ARCHIVE,CSV,MERGE,FEDERATED,ISAM,BLACKHOLE

 Transactional storage engines supports transactions like COMMIT, ROLLBACK,SAVE POINT. Transaction is noting but a collection of executable sql statements. And Transactional storage engines supports foreign key constraint.

MyISAM is a MySQL default storage engine(Before Mysql 5.5 version). It is a non-transactional storage engine. It doesn't support transactions. It process the insert and select statements as fast as possible. It supports fulltest index supporting. And also table level locking. No foreign key support. It supports 64 indexes per row. Count(*) works fast than Innodb. We can compress the data here.>create table emp(id int(10),name varchar(30),sal double(14,2)) engine=”myisam”;

mysql> create table emp2(id int(10),name varchar(30),sal double(14,2),FULLTEXT(name))

here we can see emp.frm,(It contains structure) emp.MYI (it contains indexex) ,emp.MYD(it contains data)..So take disk space more.

Innodb is a MySQL default storage engine (From 5.5 version). It is a transactional storage engine. It supports transactions and row level locking. It provide ACID properties. It process update queries as fast as possible. Count(*) little slow than MyISAM. It does't support fulltext index. Here we can't compress the data because every thing store in a single file(ibdata).

Atomicity : a transaction is a unit of operation - either all the transaction's actions are completed or

Consistency : Any work in progress must not be visible to other transactions until the transaction has

Isolation : A transaction should appear to be running by itself,the effects of other ongoing transactions must be invisible to this transaction,and the effects of this transaction must be invisible to other ongoing transaction.

Durability : When the transaction is committed,it must be persisted so it is not lost in the event of a failure.Only committed transaction are recovered during power-up and crash recovery;uncommitted

>create table emp(id int(10),name varchar(30),sal double(14,2)) engine=”innodb”;

mysql> create table dept(eid int(10),dname varchar(30),loc varchar(30),foreign key(eid) references

here we can see only emp.frm. The data and indexes will be store in ibdata file. innodb_file_per_table supports from mysql 5.5 to stores index and data in .ibd file separately for every table.

mysql> alter table emp2 engine=innodb;

MEMORY (formerly known as HEAP)

For this engine the data will be stored in the memory. When we restart the server server we will loss all the data. When we create a table only .frm file will be store in data directory. It does't support For this engine the data will be store in csv comma separated values.This engine stores data in zip format. It does't support priamary key. It is use to stores historical data. The select statements will be very slow because the data will be stored in compressed format.The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information.

mysql> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE t2 (a INT NOT NULL

AUTO_INCREMENT PRIMARY KEY,message CHAR(20))

ENGINE=MyISAM;

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a))ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST/LAST;

FEDERATED

The FEDERATED storage engine is used to access the remote MySQL database without using replication or cluster technology.

> create table emp(id int(10),name varchar(20),sal double(14,2));

>create table emp(id int(10),name varchar(20),sal double(14,2) )connection='mysql:://

root:root@192.168.1.2:3306/exdb/emp';