[support] MyISAM vs InnoDB

[Ivan Sergio Borgonovo]

On Mon, 23 Mar 2009 16:32:34 +0100
Daniel Carrera <daniel.carrera at theingots.org> wrote:

> Actually, I would love to know what those reasons are. Is it just
> data integrity?
> 
> Our web host doesn't offer Postgres, so I can't seriously consider
> that option, but I would be happy to hear more about the pros and
> cons, just out of personal curiosity. You seem to suggest that
> MySQL has faster reads for small databases. How small is "small"?
> My boss likes speed. :)

MyISAM, InnoDB and PostgreSQL perform differently on different
situation.
Ease of development and safety of data may have an indirect impact
on speed as well.

For some tasks MyISAM is reliable enough and "failure" won't impact
the "average" speed. Once you add replication it may become your
best solution.

I think that outside that scenario the only 2 good reason to chose
InnoDB over PostgreSQL are:
- you invested a lot in MySQL (your staff is made of MySQL black
  belt)
- you want to lower as much as possible the cost of maintaining
  drupal sites and still data integrity and frequent writes aren't
  your first priority but you're starting to feel the pain of
  MyISAM. In my view most of the time this is going to be a
  "diplomatic" choice... still not a "we seriously would like a bit
  more data integrity/concurrency but we can't afford PostgreSQL"
  choice. Once you're in the territory of "a bit more data integrity
  and concurrency" you've already passed the threshold that will
  make maintaining MySQL cheaper.

So most of the times the real reason to keep using InnoDB vs.
PostgreSQL is your knowledge of the tool.

In most if not all the situations where you may chose InnoDB over
MyISAM just on technical merits, PostgreSQL would be a better
candidate... since there are other constraint that may influence
your choice you may still prefer InnoDB.

And... as Michael Prasuhn wrote most of the sites don't have to deal
with the problems InnoDB try to solve and when they run into them
they have built up so much (too much?) MySQL knowledge and invested
so much in MySQL tuning/coding that their best move is to switch to
InnoDB.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it