1. InnoDB as default storage engine since MySQL 5.5.5
2. InnoDB vs MyISAM
3. Configuring InnoDB

Environment :

• Relational database management system : MySQL
• Storage engine :  InnoDB
• Operating system : Microsoft Windows

1 InnoDB as default storage engine since MySQL 5.5.5

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB. This change applies to newly created tables that don’t specify a storage engine with a clause such as ENGINE=MyISAM.

This means that, with modern WAMP stacks , InnoDB is the default storage engine. However, highly configurable WAMP stacks like EasyPHP offer the possibility to change the storage engine. You can switch from InnoDB to MyISAM and vise versa in one click through the administration page.

Sources :

• http://dev.mysql.com/doc/refman/5.6/en/innodb-default-se.html
• http://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html
• http://www.easyphp.org

2 InnoDB vs MyISAM

If you have to choose between InnoDB and MyISAM, the first step is to determine if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.

MYISAM:

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

To summarize the differences of features and performance

• InnoDB is newer while MyISAM is older.
• InnoDB is more complex while MyISAM is simpler.
• InnoDB is more strict in data integrity while MyISAM is loose.
• InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
• InnoDB has transactions while MyISAM does not.
• InnoDB has foreign keys and relationship contraints while MyISAM does not.
• InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
• MyISAM has full-text search index while InnoDB has not.

In conclusion

• InnoDB is more suitable for data critical situations that require frequent inserts and updates.
• MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

Sources :

• http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html
•  http://www.rackspace.com/knowledge_center/article/mysql-engines-myisam-vs-innodb
• http://stackoverflow.com/questions/277440/mysql-myisam-vs-inno-db/277445#277445

3 Configuring InnoDB

See :

• http://mysqldump.azundris.com/archives/78-Configuring-InnoDB-An-InnoDB-tutorial.html
• http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/