Database engines in MySQL. MyISAM and InnoDB

What is a Database Engine?
A database engine is basically a software layer in every DBMS that creates table, updates them and retrieves information from them. Since data and database may be of different natures MySQL DBMS provides several kinds of DBMS engines. We discuss two of them, that are most popular-

1)MyISAM

MyISAM is the default database engine of MySQL, that is based on ISAM (Index Sequential Access Method). ISAM was developed by IBM for fast retrieval of data in Mainframe Computers. MyISAM is fast in data access and supports full text searching. The limitation of this is, MyISAM is not suitable for transactional tables or it does not support foreign key constraints. (i.e. referential integrities). This is poor in crash recovery also. MyISAM locks the table until the operation is not complete. For example if you are inserting a row, the next operation say ‘updating of row ‘will wait until the first finishes.

2) InnoDB

InnoDB is more safe database engine of MySQL. InnoDB is good for transactional tables. It supports foreign key constraints. COMMIT, ROLLBACK sort of operations can be performed in this engine. Crash recovery is more easy in this. This stores data in clustered indexes (Storing the rows in primary key order while MyISAM stores the rows as they added). InnoDB is designed for large volume of data for high performance. This is also good for multi-user concurrency.