What is the difference between MySQL table type InnoDB and MyISAM?

Following are the key differences:
* Apparently MyISAM is faster than InnoDB.
* InnoDB supports row locking, while MyISAM only supports table locking.
* MyISAM is better for high read volumes, InnoDB for high update volumes due to table vs row locking.
* InnoDB is journaled, and can recover from crashes where MyISAM can't, much like NTFS vs FAT file systems.
* MyISAM has full-text indexing, InnoDB doesn't.
* InnoDB has transaction support, commits and rollbacks, MyISAM lacks these.
* Eliminates the constant database errors that using a MyISAM table would cause from the overload. InnoDB would therefore be a tad more reliable when you don't mind taking a small performance hit in exchange for not suffering from table locking issues.

For large tables, table locking is much better than row locking for most applications, but there are some disadvantages:

* Table locking enables many threads to read from a table at the same time, but if a thread wants to write to a table, it must first get exclusive access. During the update, all other threads that want to access this particular table must wait until the update is done.
* Table updates normally are considered to be more important than table retrievals, so they are given higher priority. This should ensure that updates to a table are not “starved” even if there is heavy SELECT activity for the table.
* Table locking causes problems in cases such as when a thread is waiting because the disk is full and free space needs to become available before the thread can proceed. In this case, all threads that want to access the problem table are also put in a waiting state until more disk space is made available.

Table locking is also disadvantageous under the following scenario:

* A client issues a SELECT that takes a long time to run.
* Another client then issues an UPDATE on the same table. This client waits until the SELECT is finished.
* Another client issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, and for the first SELECT to finish.

By,

Sharif Ul Islam

Senior Software Engineer

Mannaco Microsystems, Inc

www.manna-co.com