Innodb and Myisam Engines in Mysql .

Mysql has three major storage engine so far . Myisam , Innodb and falcon (released from mysql 5.1.14) . Though falcon is easy to use but it has shown many performance degradation in its beta release . So we focus on innodb and myisam engines .

Myisam engines is the default engine in mysql . It was built with ISAM (indexed sequential access method ) . Innodb is the next engine where all used to say performance in innodb is more when compared to myisam .

Regarding to performance, innodb performs well because it has better caching system . It has clusterd index to store data ( i.e data of the table is stored in the leaves of the primary key ). You have to go through lot of secondary indexes and retrive a primary key in innodb . Even though clustered index is fast , it will come at the expense of the memory . Since it store data in the leaves of the primary key memory used for caching a table will be high . Myisam engine is not clustered indexed . Myisam stores only primary key and retrives data of the table with respect to key . With this approach there will be no memory issues but the speed innodb retrives table data is more when compared to myisam .

For any online services, availablity is more important . Innodb is crash safe when compared to myisam . Innodb supports transaction while myisam does not support transaction . Myisam writes table info in .frm files and data in .myd file and indexes in .myi file while innodb has only bin log file . So during crash innodb easily recover by replaying its bin log file . Myisam also recovers from crash but it is acheived with cost of time .
For a real time mysql server there will be GBs,TBs of data , in this case innodb recovers more faster than myisam by replaying its log files and also we can get data which was commited before crash using innodb since it flushes data to log file after each transaction commits .

Coming to concurrency innodb supports row level locking when a table is queried with respect to primary key then only the row in that particular table is locked instead of locking entire table . Innodb also suuports mutex locking . There is some threshold limit of update transactions in innodb . Since myisam does not support transaction , it does not have any overhead like this . Large number of threads entering innodb at a particular time may lead to thrashing . So innodb user must keep this in mind and plan for transaction integrity . Overall concurrency will be more in innodb if table locking is avoided instead row level locking is made . This table level locking may lead to dead lock when large number of threads try to acquire lock for a single table .

The above things seems to be a paid advertisment for innodb but it is not like that . Myisam is very useful for insert only and less select tables . For example audit and stats in application . Since auditing a application has lot of inserts and less select one can use Myisam engine for audits and stats . So your application should define which engine is used for which module .

Now in mysql 5 onwards there is maria search engine which is developed by the author of myisam engine . This is released and not stablized yet . Hope this will solve most of the needs , the main aim for the authors are to support both transaction and non-transaction modes , multi version concurrency control , enhanced indexing model with no clustered index , larger page size , flexible memory allocation . If these things are stablized then most of them will be using maria in future .