MySQL: MyISAM versus InnoDB

Last week I saw an interesting article by Craig Buckler on one of the Sitepoint blogs: "Top 10 MySQL Mistakes Made By PHP Developers". I was already aware of a few of them (for example sanitizing user input, avoiding * in SQL SELECT statements) - and I've been using the PDO (PHP Data Objects) interface for database access pretty much since I started with PHP, which made recently switching an application from an SQLite to MySQL backend almost trivially straightforward.

However the first mistake listed was something I hadn't previously come across: "#1: Using MyISAM rather than InnoDB" - which prompted me to do a bit of background research.

MyISAM and InnoDB are two of several database "storage engines" offered by MySQL (a storage engine is the underlying software that implements the low-level database operations to create, read, update and delete data - you can list all the supported engines for a MySQL installation using SHOW ENGINES). Surprisingly (to me at least) the engine type is specified at table- rather than database-level, and can be done when first creating the table:

CREATE TABLE myTable (
...
) ENGINE = MyISAM | InnoDB;

If the engine isn't explicitly specified then MySQL uses the default (MyISAM for MySQL versions before 5.5.5, InnoDB since).

The key differences between the two are:

• InnoDB supports transactions, but MyISAM doesn't. Transactions enable several SQL statements to be combined and then accepted all at the same time, essentially treating them as a single query. This helps maintain database integrity - the database state is only changed if all statements in the transaction are executed successfully.
• InnoDB uses row-level locking, whereas MyISAM uses table-level locking. This means that InnoDB offers better concurrency for tables with a high volume of updates at the same time as reads. (See another Sitepoint blog post for a real-world example of where this can make a difference: "Free Performance With MySQL Table Types".)
• InnoDB supports foreign-key constraints, which again can help maintain database integrity - operations on one table will fail if some condition - say a matching row in another table - is not met.
  
• MyISAM supports full-text indexing; InnoDB doesn't.
  

From what I understand, in the past MyISAM's principal advantage over InnoDB has been its speed, operating significantly faster especially for applications where there is a high volume of reads compared with updates. However more recent implementations of InnoDB seemed to have closed the performance gap enough that this is no longer an issue (which is presumably why MySQL now uses it by default).

New databases created using the latest MySQL will automatically use InnoDB, but for older databases you can find out what engine your tables are using (along with other information) with:

SHOW TABLE STATUS;

The storage engine can be changed using the ALTER TABLE command, e.g.

ALTER TABLE myTable ENGINE = InnoDB;

Clearly it's possible to mix tables with different storage engines, but presumably great care needs to be taken to understand the implications (for example, what happens if a transaction that involves InnoDB and MyISAM tables fails?). There's more about storage engines in the MySQL reference manual.

Returning to the original posting, Craig's rather blunt statement about favouring InnoDB over MyISAM seemed to generate plenty of controversy amongst the commenters, but from what I've read InnoDB does look to be the better choice even if you're not currently taking advantage of transactions or foreign key constraints (which I'm not). I think there's also a wider point (as hinted at in his mistake #5: "Favouring PHP over SQL"): to use something effectively, you need to take time to understand it and the context in which it's operating. So this post is my contribution to the cause - hope it helps.