MyISAM vs InnoDB….??

Storage Engines are the programs that are integrated with a MYSQL database management to manage the data tables. MYSQL supports different storage engines that handles different tables.

Selection of Storage Engine:

Selection of storage engine, depends on users table type and its purpose. Each has their own advantages and disadvantages. Also, possible to select engine at the time of creating tables.
While selecting storage engine, the factors that can affect your selection are:

• Transaction and concurrency
• Backups
• Special features

You should use INNODB for your tables unless you have a compelling need to use a different engine!! (To get High Performance)

By- Peter Zaitsev

Some of important Storage Engines are :
MyISAM, INNODB, MERGE, MEMORY, etc.

The major thing that one should know what are the difference between InnoDB and MyISAM???

MyISAM

(+)

• Designed with a thinking that database are frequently read not updated.
• Simple to understand and implement.
• Has full text searching capability.
• Good for frequent read operations(when several computations are executing simultaneously)
• Faster reads and writes for small to medium sized tables.
• Lock tables while updating.
• Count(*) queries are fast

(-)

• Non-transactional. Data loss issues during crashes.
• Table level locking
• Insert and update queries are scaleable, concurrency issues

InnoDB

(+)

• Designed with a thinking that database are frequently updated not read.
• Transaction-safe
• Data-integrity is maintained throughout the entire query process.
• Clustering by primary key for faster lookups and ordering.
• Transactional, crash-safe, better online backup capability.
• Allow multiple updates on single table
• Locks only row, not table.

(-)

• Slower writes(insert, update queries)
• Slower BLOB handling
• Count(*) queries require full table scan.

Simple comparison chart

	MyISAM	InnoDB
Required full text Search	Yes	5.6+
Require Transactions		Yes
Frequent select queries	Yes
Frequent insert,update,delete		Yes
Row Locking (multi processing on single table)		Yes
Relational base design		Yes

 

Summary:

Frequent reading, almost no writing => MyISAM
Full text search in MySQL <= 5.5 => MyISAM

In all other circumstances, InnoDB is usually the best way to go and INNODB can now use Sphinx for Full Text Searching.

Reference:

http://www.slideshare.net/gauravpaliwal/mysql-engines
http://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

Enjoy Coding! 🙂