Types Of Storage Engine In Mysql and Myisam Advantages and Disadvantages

Types Of Storage Engine In Mysql

A database engine is underlying software that a database management system uses to create, retrieve, update and delete data from database.  MYSQL database is supporting different types of engines to execute the SQL statements. 

Myisam: It is a record storage engine. It is based on older isam code but it has many useful executions.

Each Mysql table stores on disk in three different files. The files have name that beginning with the table name and have an extension to indicate the file type.

i).frm => file stores the table format.

ii).myd => files stores the table data.

iii).ind=> file stores the table index.

To create the table using myisam engine the SQL statement is 

“ create table <tname> (col1, col2..) engine=myisam

To see the engine supported by MySQL server the statement is

“show engine”

To see the table names along with the engines the SQL statement is 

“show table status”

In MYSQL table the data values are stored with the low bytes. It makes the data machine and operating system independent.

The Myisam format supports large table files up to 4 GB. It is very fast to fetch the records from the basic frequently. Simple to design and create, and no worries about the foreign key relationships. It is good for reading intensive(select) table.

Disadvantages of Myisam:

This table doesn’t supports the foreign key relationships. That is the reason why we can’t use this table in enterprise applications. It is very slow at the time of updating and modifying the table records. If the system crashed we cannot get back the date.