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
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