Archive for the ‘MySQL’ Category

MySQL storage engines: InnoDB vs MyISAM

MySQL, Web development kim | 1 Sep. 2009 | 1 comment

Last week we went to the excellent PHPbenelux meeting organised by the nice people over at Inventis and Weble. During the (fantastic!) presentation of @chrisramakers, he spent some time on database optimization, where he talked about the importance of using the correct storage engine (MyISAM vs InnoDB vs …). This is currently a hot issue in our lab team (consistency vs ease of use) so we thought we would elaborate a little on the differences between MyISAM and InnoDB.

MyISAM

  • MyISAM, based on ISAM, has been around for a long time.
  • Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.
  • table locking: this could be a problem when you have to write to the database often. When you only need to write to the database a few times but require many select statements, this should not present any problems; e.g. logtables
  • supports fulltext search
  • high speed data storage and retrieval
  • non-transactional

InnoDB

  • InnoDB is a bit newer. It is a product of Innobase, a subsidiary of Oracle.
  • InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions).
  • supports transactions with commit, rollback and crash-recovery
  • supports Foreign Key referential-integrity constraints
  • row locking: when writing and reading a lot, this can be good for your performance
  • InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.
  • views
  • stored procedures
  • triggers

Conclusion:

If you have to be careful with your resources, MyISAM is the way to go. InnoDB uses more memory, disk space and CPU space. For fulltext search you’ll need MyISAM aswell. On the other hand, if you want to be sure your data remains consistent use the foreign keys and transactions of InnoDB.

If you encounter speed problems with table locks in MySQL, you may be able to improve performance by converting some of your tables to InnoDB or BDB tables.

Very important note to this article: You can mix the usage of both storage engines in the same database. Bare in mind that you can join InnoDB tables with tables using the MyISAM engine. This means that you can have your database running mainly on InnoDB to ensure your data integrity and use MyISAM for the search tables.

Databases @ These Days:

Of course, I, “the database guru here at these days”, am quite fond of InnoDB. It reminds me of my years as an oracle developer where databases were indestructible and data inconsistency was out of the question. My more multimedia developer colleagues don’t want to hear about all those weird database ’shizzlethingies’ and just want to do their thing and get on with it …

So we need to find a balance and that’s exactly what we’re going to do. As mentioned above we’re going to think things through and decide, based on our ERD, which tables will be using which storage engine, so a mix of both worlds seems to be the best option.. Will it work? I’ll let you know… The odds are against us (2 InnoDB girls VS 4 MyISAM guyz), but I promise you we will use all our tricks to get them down ;-) After this post, they’ll already have to admit that InnoDB isn’t all bad.

Resources: