TechTrouts.com

I’ve been around choosing the appropriate Storage Engine for fallforward’s development, and so i’m posting some tips on Storage Engines for MySQL 5.

Storage engines differ essencially in how they organize the data, indexes, caching, etc.
The pratical results are different possibilities in data organization and restrictions (foreign keys, constraints, triggers (on delete, on update), etc) as well as different performance capabilities.

Technical Differences:

MyISAM is designed to be a simple and effective storage engine, ideal for small websites, blogs, etc. Doesn’t require much technical knowledge and get’s the work done. It also features full-text index, allowing you to take advantage of the very useful “MATCH col AGAINT(’needle’)” text search clause.

MRG_MyISAM is treated as MyISAM but can be “shared” among different databases – very useful for main tables in multiple-database applications, as long as your working on the same MySQL server.

InnoDB is designed to be more of a storage engine for applications. It features foreign keys, triggers, etc – essential for multi-million rows and organizational data.

Performance:

A few years back MyISAM would kick ass on SELECT clauses, whereas InnoDB was usually better for heavy INSERT / UPDATE and DELETE clauses.
These days InnoDB can be even faster than MyISAM for SELECT clauses.

Though MyISAM can be much faster for a simple “SELECT count(*) from table” query as it caches the amount of rows on a table, they work more or less the same when you use the “where some_col=’some_val’” clause. On the other hand text searching on full-text index kicks ass over any “LIKE ‘%needle%’ ” clause.

MRG_MyISAM is pretty much like MyISAM, though it can usually get a little bit slower due to multiple database usage and locking.

All in all InnoDB has come a long way these past few years, and has become my first choice for applications, though i still use separate MyISAM tables for full-text searching on text fields.