MySQL Engine: MyISAM vs Innodb

MyISAM (http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html)
-to create a MyISAM table, add ENGINE = MYISAM; at the end of create table script
-supports table locking (this is an issue when you need to backup the whole database)
-fast execution compared to innodb
-each table is stored in each own file
-has row limit
-produce more compact data
-use less storage space than innodb
-supports full-text search
-rollback is an issue

Innodb (http://dev.mysql.com/doc/refman/5.0/en/innodb.html)
-supports row locking, which is good for huge amount of concurrent inserts/updates
-tables and indexes are stored in a tablespace (multiple files)
-efficient in processing large amount of data
-supports transaction (commit, rollback, etc)
-fast query algorithms but use more memory compared to MyISAM
-choose if transaction, primary and foreign key are important for your system
-I prefer this, because of referential integrity, transactions and rollbacks
-if your database is normalized and you have lots of foreign keys, there are tables
that are dependent on others, then you need this

Benchmark of Falcon, MyISAM and Innodb
http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

Mysql Management Tools:
mysql gui tools - http://dev.mysql.com/downloads/gui-tools/5.0.html
sqlyog - http://www.webyog.com/en/downloads.php
toad - http://www.quest.com/toad-for-mysql/
MySQL Engine: MyISAM vs Innodb MySQL Engine: MyISAM vs Innodb Reviewed by czetsuya on Thursday, October 22, 2009 Rating: 5

No comments:

Powered by Blogger.