When to use MyISAM or InnoDB

[silvester]

MyISAM Limitations

* No foreign keys and cascading deletes/updates
* No transactional integrity (ACID compliance)
* No rollback abilities
* Row limit of 4,284,867,296 rows (232)
* Maximum of 64 indexes per row

InnoDB Limitations

* No full text indexing
* Cannot be compressed for fast, read-only

If none of those deal-breakers apply, then you can keep reviewing the pros and cons. Here’s some things to keep in mind:

Read to write ratio

If you find that the amount of writes climbs over 10-15%, you should consider InnoDB, but if it climbs over 20%, you will get a big performance gain by moving to InnoDB.

This all stems from how the storage engines handle locking. MyISAM uses table-level locking. That means that if you run a query against the table, the whole table will be locked. The only exception is that you can insert a row if there is an active read lock on the table, and the table is not fragmented (called concurrent insertion). So if a user is running a really slow select on a table, your updates or deletes will have to wait until the select is completely finished. This delay can reduce the overall efficiency of your application drastically if your MySQL server is busy and over 20% of your queries are writes.

InnoDB uses a row-level locking scheme, which means that waiting on locks is greatly reduced. When MySQL wants to lock something for a query in an InnoDB table, only the row that needs to be accessed is actually locked. For example, if you run a select that examines rows 5-20 in a table, but another user wants to write to row 24 at the same time, both queries will be executed simultaneously. Of course, transactions will change this behavior a bit, but I’ll leave that out for simplicity’s sake.

Available hardware
InnoDB requires additional hardware in order to function as efficiently as MyISAM. This means that you will need additional CPU power and RAM to get good performance out of InnoDB tables. MyISAM tables can also be compressed with myisampack so that they can handle queries quickly (at the expense of being read-only).

Table growth
MyISAM (by default) is limited to 232 rows, and this can be a pain when you reach the limit. You can increase it manually when you reach the limit, but this causes the row pointer to become gigantic, and this reduces performance. InnoDB tables have no limits, and they can grow over 2GB even on systems that don’t allow files to grow over 2GB. InnoDB tables can also use raw partitions to store data, and this ends up being quite fast relative to simple ibdata files.

Isolation and integrity
When it comes to transactional integrity, InnoDB holds all the cards. You can adjust your isolation levels so that you see the exact data you want, even when data is changing from other transactions when your transaction runs. Transactions can have save points to which you can roll back if something does not work out. Also, if you have the need to run several queries at once that need to run uninterrupted, transactions are required for this functionality.

[latha]

Great Post Silvester.

Which one does wordpress use by default? Can we switch between one type to the other in MySQL?

[beniston]

Nice post Silvester. Now only I could read your post which was posted a long time back.


By default Wordpress supports MyIsam.

Yes its possible to switch between one to the other by regenerating the whole database (but with the loss of the features of the one from which you are switching). One should be very careful of this switching as they may cause bugs in the application when a feature found in a type (for example, cascading) is missing in the other type.