Difference between MyISAM and InnoDB Engine

1. InnoDB supports foreign keys where MyISAM does not.
2. MyISAM use table level locking and InnoDB supports row level locking.
3. MyISAM are probably best suited for Data marts and small specialized data warehouses,InnoDB would be a good solution for say a real time data warehouses where the inserts need to be more frequent than daily
4. MyISAM beter to use for tables that have alot of SELECT transactions and don't have alot of INSERT or UPDATE queries, but InnoDB better for opposite situation.
5. MyISAM is easy to recover but InnoDB not
6. MyISAM quite easy in maintenance than InnoDB.
7. MyISAM is prone to corruption than innodb.

As a general ACID compliant DB engine the InnoDB seems to be best bang for the buck, but when used in scale has fairly high resource costs and still have concurrency issues with manipulating large data sets. MyISAM use table level locking and have no transaction support it makes this engine unsuitable for either OLTP or larger data warehousing cases. MyISAM needs to be used in cases where your active dataset cannot fit into system memory.

• MyISAM is a disk based storage engine. Aiming for very low overhead, it does not support transactions.
• InnoDB is also disk based, but offers versioned, fully ACID transactional capabilities. InnoDB requires more disk space than MyISAM to store its data, and this increased overhead is compensated by more aggressive use of memory caching, in order to attain high speeds.
• Memory (formerly called "HEAP") is a storage engine that utilizes only RAM. Special algorithms are used that make optimal use of this environment. It is very fast.
• NDB, the MySQL Cluster Storage engine, connects to a cluster of nodes, offering high availability through redundancy, high performance through fragmentation (partitioning) of data across multiple node groups, and excellent scalability through the combination of these two. NDB uses main-memory only, with logging to disk.

If you use CREATE TABLE without specifying the ENGINE option, the server will use the default. The default storage engine is MyISAM.
If you want to change the default to say InnoDB, you can use the configuration directive default-storage-engine=InnoDB.

You can change the storage format of an existing table.
ALTER TABLE t1 ENGINE=InnoDB;

These commands make the server create the proper table structure, indexes, and copy all the data. Do note that not all storage engines support all column and index types, so conversion is not practical in all instances.

MyISAM
MyISAM has been the default storage engine since version 3.23. With MyISAM, each database is a directory, with each table stored in a separate set of files.
For an individual table, the .frm file contains information about the table structure — effectively, an internal representation of the CREATE TABLE statement. The .MYD file contains the row data, and the .MYI contains any indexes belonging with this table, as well as some statistics about the table. The data file contains only row data, with minimal overhead.

MyISAM actually offers a number of storage and indexing options within its basic file format. We will cover the ones that are most commonly used, so that we can compare them with each other and the characteristics of the other storage engines.

The MyISAM "fixed" row format is automatically selected if the table structure does not contain any VARCHAR, TEXT or BLOB type columns. In this case, each column has a fixed length, and as a consequence, each row will be the same length. This means that if any rows are deleted, they will only ever be replaced by rows of the exact same length, so rows never become fragmented. Also, the storage engine will be able to access individual rows by their "row number" instead of a complete file offset. This makes any indexes smaller, and the overall system faster and more memory efficient. Please note that the "row number" mentioned here is purely internal, as rows in relational database tables are by definition unordered.


InnoDB
The InnoDB storage engine has a completely different architecture. InnoDB uses the concept of a tablespace, where all structure, table data and indexes are stored. A tablespace can consist of one or more files, even raw disk partitions. Recent versions of MySQL also offer an alternate mode, where InnoDB stores information belonging to a specific table in a single file, i.e. One file per table.
Concurrency control in InnoDB is handled by its complete support ACID transactions, multi-versioning, row-level locking, and foreign key constraints. For an in-depth discussion of these characteristics, see the article on "Concurrency Control".