MyISAM Database Engine Vs InnoDB Database Engine

In MySQL Server, there are two types of major storage engines: MyISAM & InnoDB. It is very important for any user to choose the right database engine because all database engines have unique advantages and disadvantages against each other. So it is most important to you to select the right database engines to fulfill all your requirements. You can also check all available database engines by using following the below commands as follow:

mysql> SHOW ENGINES\G;

These are available database engines in MySQL Server:

InnoDB   
MRG_MYISAM
BLACKHOLE 
CSV       
MEMORY
FEDERATED
ARCHIVE  
MyISAM

MyISAM Engine: This Database Engine stored tables in three files: frm(stores table format), myd (data file), myi (Index file). If you would like to create table in MYSQL server then you have to mention the engine name just like this:

CREATE TABLE t (i INT) ENGINE = MYISAM;

You can also check & repair MyISAM tables by using inbuilt utility such as mysqlcheck & myisamcheck.

If your insert|update ratio is less than 15% then it is better to use MyISAM Database Engine.

Limitations:

No Foreign keys and cascading deletes and updates
Rollback abilities are not available
No transactional integrity
Row limit is 232
There are Maximum of 64 indexes per row


InnoDB Engine: This Database Engine stored tables in three files: frm(stores table format), ibdata (stores user data and indexes), Ibd (tables and indexes are stored in their own tablespace)). If you want to create table in MYSQL server then you have to mention the engine name just like this:

CREATE TABLE t (i INT) ENGINE = InnoDB;

You can also check & repair InnoDB tables by using CHECK TABLE & REPAIR TABLE command. InnoDB uses lots of features to protect user data such as row level locking, has commit, rollback, and crash-recovery capabilities. It also supports transaction and fault tolerance.

Limitations:

No full text indexing
Cannot be compressed