MYISAM and InnoDB

What is difference between MYISAM and InnoDB?
No. MYISAM INNODB
1 MYISAM supports Table-level Locking InnoDB supports Row-level Locking
2 MyISAM designed for need of speed InnoDB designed for maximum performance when processing high volume of data
3 MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4 MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI) InnoDB stores its tables and indexes in a tablespace
5 MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.  InnoDB supports transaction. We can commit and rollback with InnoDB
6 MYISAM supports fulltext InnoDB does not support fulltext
7 You can use MyISAM, if the table is more static with lots of select and less update and delete. The only viable fast backup option, which can also be used to populate new slave machines, is InnoDB Hot Backup.
8 MySQLDump backup is too slow with InnoDB. (If you insist on using it, turn on these flags: –opt –compress) Doing a tar/rsync backup where you simply copy all files is not possible with InnoDB.
9 LOAD DATA INFILE is too slow with InnoDB. Consider using MyIsam tables for LOAD DATA operations. InnoDB has built-in recovery that works 99% of the times automatically. Never try to move .frm or .ibd files around as a way of "helping" the database to recover. If the built-in recovery doesn’t work, switch to your slave server and restore the primary from backup.
10 Never ever change my.cnf INnoDB log file size while the database is running. You’ll corrupt the log sequence number beyond repair. InnoDB is less forgiving than MyIsam when it comes to queries on non indexes. InnoDB is going to "School" you into ensuring every single query and update statement runs on an index. Issue no index queries and you’ll pay dearly in execution time.
11 If InnoDB crashes and the built-in recovery mechanism is unable to roll-back transactions, your database will not start. This is very important to understand. With MyISAM, even if a table gets corrupted, you can still start the database and everything will work normally. InnoDB will simply refuse to start until you restore the entire database from backup. Make sure you understand this principle and backup religiously.
12 For read-heavy environments, use an NDBCluster or setup replication for n MyISAM slave read-only machines. For write-heavy environments, InnoDB on an active/passive replication setup is typically the best choice. You may also want to experiment with an NDBCluster. An NDBCluster is generally going to be slower than InnoDB in write-heavy environments, but it offers a higher level of availability.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>