- Crash recovery can be a time-consuming process owing to MyISAM's lack of a transaction log. Expect to have to perform such a recovery during your peak-usage period. (If it can happen, it almost certainly will.)
- MyISAM does not support or enforce foreign key constraints.
- All UPDATE queries to the same table are serialised — that is to say they carried out one at a time — and block all other queries, including SELECTs, from executing. This effect is pronounced on most busy multi-user applications.
- MyISAM supports concurrent INSERTs only in certain cases.
- MyISAM tables created on 32-bit operating systems or using MySQL releases earlier than version 5.0.6 will be limited to 4 GB in size. This limit can be overcome after migrating to a 64-bit host or updating the MySQL server to a later software release.
- Your application demands full-text search capabilities. Rather than push all your data into MyISAM tables to gain full-text searching, it may be viable to split your dataset into data that must be indexed for full-text searching — and stored using MyISAM — and data that should be stored using a transactional engine, such as InnoDB. A scheduled background job may then asynchronously update your MyISAM full-text indexes and provide links from the InnoDB data as appropriate. This is a common example of how to gain the best from all worlds.
- Your application is effectively single-user — there are very few concurrent queries hitting the MySQL server.
- You are performing limited testing or development where performance is not under scrutiny.
- Queries that result in large scans of the tablespace are often slower when using InnoDB.
- Consistency is only maintained if the underlying operating system and hardware can guarantee buffer flushes. This limitation is inherent in all transactional database management systems.
- InnoDB tables consume a greater amount of space on-disk than their MyISAM equivalents. This is now largely irrelevant given the ubiquity of large (multiple hundreds of gigabytes) hard disk drives.
When to use InnoDB
- You are developing an application that requires ACID compliance. At the very least, your application demands the storage layer support the notion of transactions.
- You require expedient crash recovery. Almost all production sites fall into this category, however MyISAM table recovery times will obviously vary from one usage pattern to the next. To estimate an accurate figure for your environment, try running myisamchk over a many-gigabyte table from your application's backups on hardware similar to what you have in production. While recovery times of MyISAM tables increase with growth of the table, InnoDB table recovery times remain largely constant throughout the life of the table.
- Your web site or application is mostly multi-user. The database is having to deal with frequent UPDATEs to a single table and you would like to make better use of your multi-processing hardware.
CREATE TABLE innodb_example ( ... ) ENGINE = InnoDB;Converting a single table from MyISAM to InnoDB
SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'; ALTER TABLE table_name ENGINE = InnoDB;
CREATE DATABASE egads; CREATE TABLE batman ( foo INTEGER AUTO_INCREMENT, bar BOOLEAN, baz CHAR(255) CHARACTER SET ascii COLLATE ascii_general_ci, CONSTRAINT PRIMARY KEY (foo) ) ENGINE = MyISAM;
import MySQLdb import random conn = MySQLdb.connect (host = "localhost", user = "root", passwd = "moo", db = "egads") cursor = conn.cursor () for i in range (1, 10000000): cursor.execute ('INSERT INTO batman (bar, baz) VALUES (' + str (random.randint(0,1)) + ', ' + str (random.randint(0,999999999)) + ');') print i cursor.close () conn.close ()
mysql> show table status like 'batman'; | Name | Engine | Data_length | | batman | MyISAM | 2609999739 |
# ls -lhF egads/ total 2.6G -rw-rw---- 1 mysql mysql 8.5K 2008-11-24 15:57 batman.frm -rw-rw---- 1 mysql mysql 2.5G 2008-11-24 16:14 batman.MYD -rw-rw---- 1 mysql mysql 98M 2008-11-24 16:14 batman.MYI
$ time echo 'SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'; USE egads; ALTER TABLE batman ENGINE = InnoDB;' | mysql; real 7m34.190s user 0m0.008s sys 0m0.004s
mysql> show table status like 'batman'; | Name | Engine | Data_length | | batman | InnoDB | 3038773248 |