Monday, February 7, 2011

MyISAM V/S InnoDB

MyISAM

Let's start with MyISAM since it is the default engine with MySQL. MyISAM is based on the older but proven ISAM code but has been extended to be fully-featured while retaining the reliability. 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.

The maximum number of rows supported amounts to somewhere around ~4.295E+09 and can have up to 64 indexed fields per table. Both of these limits can be greatly increased by compiling a special version of MySQL.

Text/Blob fields are able to be fully-indexed which is of great importance to search functions.

Much more technical information can be found on MySQL's MyISAM Manual Page.

InnoDB

InnoDB is relatively newer so the scene than MyISAM is so people are still weary about its use in environments than run fine under MyISAM. InnoDB is transaction-safe meaning data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-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.

Another great feature InnoDB boasts is the ability to use foreign-key constraints. FK constraints allows developers to ensure that inserted data referencing another table remains valid. For example, if you had an authors table and a books table and you wanted to insert a new book while referencing the author. The author would have to exist in the authors table before inserting them in the books table because a foreign key was specified in the books table. At the same time you would not be able to delete an author from the authors table if they had any entries associated with them in the books table. More on this in a later article...

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.

Comparison

MyISAM in most cases will be faster than InnoDB for run of the mill sort of work. Selecting, updating and inserting are all very speedy under normal circumstances. It is the default engine chosen by the MySQL development team which speaks to its integrity, reliability, and performance.

InnoDB, or the OSX of the database-engine world, has emerged with some nifty features and created a niche for itself very quickly. Boasting features like row-level locking, transaction-safe queries, and relational table design are all very temping. The first two features really shine in a table that is constantly getting hammered like a logs, or search engine-type table. Since queries happen in the blink of an eye (faster actually) table-level locking(MyISAM) is sufficient in most other normal cases.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.

Decision Matrix

Is your table is going to be inserted, deleted, and updated much much more than it is going to be selected?
InnoDB
If you need full-text search MyISAM
If you prefer/require relational database design InnoDB
Is disk-space or ram an issue? MyISAM
In Doubt? MyISAM

There is no winner.

REMEMBER! It's OK to mix table types in the same database! In fact it's recommended and frequently required. However, it is important to note that if you are having performance issues when joining the two types, try converting one to the other and see if that fixes it. This issue does not happen often but it has been reported.

1 comment:

  1. This is a nice article..
    Its very easy to understand ..
    And this article is using to learn something about it..
    c#, dot.net, php tutorial
    Thanks a lot..!

    ReplyDelete