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! ๐Ÿ™‚

 

Advertisements

2 thoughts on “MyISAM vs InnoDB….??

  1. Pingback: MyISAM vs InnoDB – Web development – programming idea

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s