In particular, how do you choose between MyISAM and InnoDB, when neither is missing a required feature (e.g. you don't need foreign keys).

Does it always come down to trying both and measuring? Or are there good rules of thumb regarding the number and frequency of reads versus writes, and other measures like that? Does the size of the table have any effect on the typical choice?

5 Answers 5


The answer is you should always measure, preferably with your own data and workload if at all possible.

Since the data access patterns can vary greatly from app to app, it's hard to say and in all likelihood impossible to determine a "best" storage engine for all workloads.

However, there are very encouraging developments in the MySQL space having attended MySQLConf/Percona Performance Conf last week.

Some of the alternative storage engines:

  1. XtraDB (fork of InnoDB)
  2. InnoDB plugin
  3. PBXT
  4. TokuDB

Additionally, Percona, Google, etc. have contributed patches that help greatly with InnoDB performance. Personally, I run an OurDelta build. It works nicely for me and I encourage checking out the OurDelta and Percona builds.

  • If you are interested in benchmarks, try sysbench or iibench.
    – Jauder Ho
    Commented Apr 30, 2009 at 9:39
  • Are any of the alternative storage engines stable enough that they are suitable for a production site?
    – Tony Meyer
    Commented Apr 30, 2009 at 10:09
  • Don MacAskill is looking at putting XtraDB into production. YMMV.
    – Jauder Ho
    Commented Apr 30, 2009 at 12:05
  • Performance is not the only requirement - consider operational issues as well (in fact they are usually more important, in my experience)
    – MarkR
    Commented May 18, 2009 at 9:26
  • Obviously, performance is not the only requirement although I believe the original request was asking more about perf. Other considerations such as operational (as you point out) and features will all play a part in the selection process.
    – Jauder Ho
    Commented May 19, 2009 at 20:09

If it's just a simple store / report system I use MyISAM for its raw performance.

I'd use InnoDB if I was concerned about multiple concurrent accesses with lots of writes, to take advantage of row-level locking.

  • 1
    For any workload that mixes reads and writes InnoDB is the only option (that is currently shipping). Commented Apr 30, 2009 at 10:45

There are a good number of benchmarks out there for different MySQL database engines. There's a decent one comparing MyISAM, InnoDB and Falcon on the Percona MySQL Performance Blog, see here.

Another thing to consider between the two aforementioned engines (MyISAM and InnoDB) are their approaches to locking. MyISAM performs table-locking, whilst InnoDB performs row-locking. There are a variety of things to consider, not only downright performance figures.


There are features that you will find very useful, for operational reasons, even if your application doesn't absolutely require them:

  • InnoDB has MVCC which means you can take non-blocking consistent backups
  • InnoDB has automatic recovery which means no lengthy REPAIR TABLE operations after an unclean shutdown
  • With InnoDB, readers never block writers and vice versa, meaning (generally speaking) greater concurrency (this need not mean better performance in the general case though)
  • InnoDB clusters its rows on the primary key, which MAY mean fewer IO operations for read operations IF the primary key was chosen sufficiently well.

So notwithstanding foreign key constraints, you probably want to use InnoDB anyway.

of course this is ServerFault, not Stack Overflow, so the proper answer is:

  • You must always use the engine that the application developers have chosen
  • If they haven't chosen a specific engine, they aren't very serious about using MySQL, and probably don't know how to use it properly.
  • You can't switch to a different engine to the one which your application was tested on, it may introduce bugs.
  • 2
    Do you really think that the database engine is the developer's decision, not the administrators? As a developer, I want to say "I have this data, that I will be doing this with", and leave optimising (and backing up, and ...) of the database to the administrator.
    – Tony Meyer
    Commented May 18, 2009 at 10:20
  • 1
    Yes, the developer needs to be able to develop and test their app against a specific engine; they behave vastly differently both functionally and in performance.
    – MarkR
    Commented May 27, 2009 at 21:46

My hosting provider advised us to get rid of MyISAM completely and switch to InnoDB, unless it is not possible.

In our case we were having severe data corruption which started to show from a few times to a few times per day, always requiring REPAIR TABLE and related commands, which took ages on large tables.

Once we converted (or: were converted) to InnoDB, the problems instantly went away. Downsides/caveats we had:

  • Can't convert tables with FULLTEXT index (this problem went away over time by itself; it was replaced with a Solr/Lucene based solution which has much better quality anyway)
  • Big tables with millions of rows which often require COUNT(*) were very slow, we were not able to switch those either.

But note: this is all specific to our environment, etc., so it may not generally apply.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .