MySQL Storage Engines (Part 1)

Below are the few Storage engines discussed and remaining will be discussed in next part

ISAM

-Original storage engine. was only available till 3.23

-Depreciated in 4.1 and completely removed in 5.0

-It was replaced by MyISAM.

-ISAM table can be easily converted to MyISAM table by using alter table command only

i.e. alter table <table_name> type=MYISM;
   alter table <table_name> engine=MYISM;

-Each ISAM table is stored on disk in form of 3 individual files

1. Table definition <table_name>.frm

2. Table data <table_name>.isd

3. Index file <table_name>.ism

Properties

Compressed and fixed length keys

Fixed and dynamic record length

16 indexes per table with 16 key per key

Data values stored in machine format i.e. fast but machine o/s dependent

MyISAM

-Default storage engine from mysql 3.23 onward

-Each MyISAM table is stored on disk in form of 3 individual files

1.Table definition <table_name>.frm

2.Table data <table_name>.myd

3. Index file <table_name>.myi

-explicit use of MyISAM storage engine

CREATE TABLE TEST (i INT) ENGINE=MYISAM;

-Supports below features

True VARCHAR datatype, a VARCHAR column starts with its length stored in two bytes

Tables with VARCHAR may have fixed or dynamic record length

VARCHAR and CHAR columns may be up to 64KB

Hashed computed index can be used for applying UNIQUE constraints

HEAP

HEAP storage engine create table whose contents are stored only in memory

before 4.1, memory tables were called HEAP tables

HEAP is synonym for MEMORY

Syntax

CREATE TABLE TEST (i INT) ENGINE=HEAP;

Memory tables are stored in memory and uses hash index which makes it fast and useful for creating temporary tables

but when server shuts down all data get flushed but table definition will remain intact as it gets stored on disk in the .frm file

MERGE
Introduced from 3.23.25, also know as MRG_MYISAM
A MERGE table is collection of identical MYISAM tables that can be used as one.
when MERGE table is created it create two files on disk
<table_name.frm>–table definition
<table_name.mrg>–names of identical tables
DROP TABLE command only drops MERGE specification is dropped not underlying tables

INNODB

This provides MySQL with transaction safe(ACID) data storage engine with commit,rollback and crash recovery capabilities

INNODB does record locking at row level and also provides an Oracle-Style consistent non locking reads in select statement

supports FOREGIN KEY constraints

it maintains own buffer pool for caching data and indexes in main memory

It stores its data in tablespace which may consist of several files or raw disk partitions

InnoDB tables can be of any size

InnoDB is being used at numerous large database sites requiring high performance

Syntax

CREATE TABLE TEST (i INT) ENGINE=InnoDB;

go to part 2