Types of Storage Engines in Mysql

Storage for MYISAM Tables :

1.When ever we create a table in database it creates a directrory with that name and stores in DATADIR location.
2.For MyISAM tables it stores tables it stores table information in 3 files (.frm,.MYD,.MYI).
3..frm files –> stores MYISAM skeletal structure of tables.
4..MYI files –> stores MYISAM index information.
5..MyD files –> stores MYISAM tables data.

Storage for INNODB Tables :

1. Data is stored in tables spaces.
2. Tables space contains pages with default size 16k.
3. These Pages are grouped into extents of size 1mb each(64 pages)
4. These Extents are grouply known as SEGMENTS.
5. When a segment starts growing it individually allocates first 32 pages,after that it will allocate whole extents to segments.
6.Default location for innodb table space is DATADIR.
7.it is created with a default size 10m later can be resized.
8.it is also creates two transaction log files which supports for innodb tables with default size of 5m;
9. innodb-data-file-path = /ibdata/ibdata1:15m:autoextend
10. the format of the tables is stored in <table name>.frm files in DATADIR location and the data is stored in TABLE SPACE.

Storage for BERKELEY DB AND MEMORY STORAGE Tables :

1.Each BDB table is stored on disk in two files (.frm and .db)
2..frm files –> stores table format information.
3..db files –> stores indexes and data information.
4. By default it runs with auto commit mode to disable auto commit give ‘set auto commit =0’
5.MySQL requires a primary key in each BDB table so that each row cab be uniquely identified. if you don’t Create      one explicitly by declaring a PRIMARY KEY,MySQL creates and maintains a hidden primary key for you.
6.To support transaction rollback , the BDB storage engine maintain log files.(dbd.logdir).