InnoDB Vs MyIsam

So,many of us who use MySQL are familiar with these terms.Only familiar!!!!!!…right??

Have you ever wondered about what these terms actually mean and difference b/w them?No issues..”Genesis” is all about unlocking such issues….So letz start…

Image

These 2 are types of storage engines.So 1st question

What is a Storage Engine?

  1. The storage-engine is what will store, handle, and retrieve information for a particular table.
  2. MySQL can use several different formats for storing data on disk, these are called storage engines or table types. phpMyAdmin allows a user to change their storage engine for a particular table through the operations tab.These formats means sometimes you want temporary data(hash based),sometimes you want row level locking,page-level locking,or u want your data should be stored remotely etc
  3. You can select the storage engine to use on a server, database and even table basis, providing you with the maximum amount of flexibility when it comes to choosing how your information is stored, how it is indexed and what combination of performance and functionality you want to use with your data.

The figure below is self explanatory and simply explains position of storage engine in mysql architecture!!!!

Image

4.The interface between the second and third layers is a single API not specific to any given storage engine. This API is made up of roughly 20 low-level functions that perform operations such as “begin a transaction” or “fetch the row that has this primary key” and so on. The storage engines don’t deal with SQL or communicate with each other; they simply respond to requests from the higher levels within MySQL.

Now letz start with MyISAM(salient features)

  1. ISAM(Indexed Sequential Access Method) .It was the only storage engine available until MySQL 3.23, when the improved MyISAM engine was introduced as the default. ISAM is deprecated.
  2. Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.
  3. Text/Blob fields are able to be fully-indexed which is of great importance to search functions
  4. it lacks transaction capabilities.
  5. uses table-level locking.
  6. With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.

InnoDB(salient features)

  1. Relatively new,so ppl are still unsure about itz performance and reliability.
  2. transaction-safe meaning data-integrity is maintained throughout the entire query process.
  3. InnoDB  provides row-locking, meaning while one query is busy updating or inserting a row, another query can update a different row at the same time. These features increase multi-user concurrency and performance.
  4. Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

InnoDB  vs MyISAM

1) InnoDB maintains excellent data integrity at the cost of storage engine housekeeping, If you value your data, you can easily live with that.
MyISAM, being free of such overhead, can be used in a concentrated way to perform lightning-fast SELECTs at the cost of data loss in the event of a crash, especially if tables are set with ROW_FORMAT=DYNAMIC (default).

You should store all static data to MyISAM that is 98% SELECT and 1% UD (Update,Delete) and 1% INSERTs to rows that need not be updated.

You should store all transactional data, whose data integrity matter from an application standpoint, to InnoDB. You can harness both storage engines well in any application when using them together for the right reasons.

2)Innodb supports transactions, MyISAM does not

3)Row-level locking and MVCC in inno db while table level locking in MyISAM

There are many more differences which even I could not understand…If any one finds some more difference plz do leave a comment.But the differences should be in layman terms.

you can refer the following sources:

1)http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/notsowellknown-differences-between-myisam-and-innodb-mysql-server-storage-engines.php

2)http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Advertisements

2 Comments

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s