

The MySQL MyISAM and InnoDB engines and a grocery checkout - itsderek23
http://blog.scoutapp.com/articles/2009/10/09/choosing-between-the-mysql-myisam-and-innodb-table-types
There's plenty of discussions centered on the choice between the MySQL MyISAM and InnoDB engines. I've illustrated a case that has bitten me in the past.
======
idlewords
The analogy is helpful, but leaves some things out. In one of the grocery
stores, you'll find that if the lights flicker off and for just a second, half
the products on the shelves may end up unusable. And if your store gets past a
certain size, a sinkhole may appear and suck most of your inventory
underground with no warning.

The only reason I can think of for using MyISAM tables in a web app is the
terrible default InnoDB settings in MySQL. Users unfamiliar with InnoDB may
not realize that the awful performance is an artifact of bad configuration,
and easily fixed.

~~~
towndrunk
Can you provide some info or a link on this config change.

Thanks!

~~~
idlewords
This is handy: [http://www.mysqlperformanceblog.com/2007/11/01/innodb-
perfor...](http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-
optimization-basics/)

Lots of good InnoDB-related posts on their site.

~~~
towndrunk
Good stuff to know. Thanks.

------
AdamN
Man this is annoying:

1\. The MyISAM/InnoDB debate is so stale it stinks. If you're using MySQL, you
should be using InnoDB unless you know exactly why you should use MyISAM. None
of these issues are relevant until you have alot of concurrency and
uptime/transactions/integrity are radically more important than potentially
faster SELECT statements on MyISAM.

2\. Did HN really just put this flaccid article to the top of the list? Aren't
we more technical than this?

~~~
spudlyo
I'd vote it down if I could, it's far too fluffy and short for my tastes. The
only reason I could think of using MyISAM is if you need features that only it
supports, like compression, MRG_MyISAM tables, or full text search.

Having said that, the newer InnoDB plugins support compression, with
partitioning you don't need MRG_MyISAM, and you can use something like Sphinx
for full text searching, which integrates nicely into MySQL.

------
modoc
I'm still not sure why folks still use MyISAM. I'm sure there may be some edge
case where it makes more sense, but in generally InnoDB seems like a better
choice (not just regarding row level locking). Or postgres:)

~~~
DonnyK
I've yet to find a good comparison of the two.

In fact, from what I've seen it seems better to use MyISAM when you don't need
support for ACID transactions.

~~~
idlewords
I don't agree. MyISAM tables are easy to trash, and the table locking on
update can bite you. There's no real disadvantage to using InnoDB other than
having to learn to tune it a litte bit, and your data will be much safer.

~~~
chrisbolt
Define 'easy to trash'. I've been using MyISAM without problems for years now.

~~~
spudlyo
MyISAM is like the ext2 filesystem. You crash and you end up spending a long
time doing an fsck, where you have to verify the consistency by walking all
the data structures. InnoDB is more like ext3, where you can make things
consistent by replaying the changes in the journal. Doing repair on a large
MyISAM table can take hours and hours.

