Lars Pind

internet software, coaching, and entrepreneurship

Lars Pind - internet software, coaching, and entrepreneurship
Check out Coach TV, my video blog on happiness and personal development for geeks.
← No matter how great your interface... Restaurant Review: Wagamama →

MyISAM or InnoDB

August 04, 2006 · 6 comments

What’s the deal with MyISAM vs. InnoDB for Rails? Here’s what I know and think I know so far:

Reasons to use MyISAM:

  • Faster according to anecdotal evidence. But is that really true, and how much does it matter given other factors?
  • Easy backups are easy with mysqlhotcopy
  • Full-text indexing

Reasons to use InnoDB:

  • Transactions
  • Foregin keys
  • Row-level locking
  • Rails defaults to InnoDB when creating new tables
  • You can delete your Rails sessions in one go without locking the whole damn sessions table

I’m leaning towards using InnoDB, because:

  • It’s the default for Rails so I don’t have to work around it
  • It has more granular locking, which is good if you plan on getting lots of traffic
  • It has transactions, which is always healthy
  • You can do backups using mysqldump, which is OK
  • You can do your searches with Ferret, which is much better than the MySQL full text index, anyway
  • You can delete your Rails sessions in one go without locking the whole damn sessions table

Is there an official recommendation? What do some of the big-site folks out there do?

blog comments powered by Disqus

6 responses so far ↓

  • 1 DHH // Aug 04, 2006 at 07:38 PM

    InnoDB wins hands down. I see MyISAM as a specialty solution for double-copy full-text indexes and for data that isn't critical but needs to be super fast (log files). I'd never put AR objects to sleep in MyISAM.

  • 2 Thijs van der Vossen // Aug 04, 2006 at 08:12 PM

    We always use InnoDB, except for when we need full-text indexes.

  • 3 Phil // Aug 04, 2006 at 08:19 PM

    In my experience MyISAM is the primary reason people make fun of MySQL, for what it's worth.

  • 4 Bob Silva // Aug 05, 2006 at 12:36 AM

    Here's a pretty good writeup on it. http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html As your tables get larger, InnoDB starts to outperform MyISAM due to the locking mechanism and concurrent inserts/selects. Best to start with InnoDB (the default transactional storage engine, not the default table type), then move to MyISAM if you have special needs as mentioned in other comments.

  • 5 Sean Treadway // Aug 07, 2006 at 12:46 AM

    I've also tried to fit MyISAM into a Rails app to get full text searching. If it's just full text indexes you're missing, Cal Henderson from Flickr has a good alternative. Keep your master data in InnoDB then replicate the tables you want to search to a search slave as MyISAM. No write lock issues and good horizontal scaling. Here's where I found the tip: http://www.niallkennedy.com/blog/uploads/flickr_php.pdf I haven't tried implementing this trick in Rails but all the pieces for custom connection for specific models are there. In any case, it should make your decision easier knowing that you can have your transactional cake and eat it too.

  • 6 random@striker.com // Aug 10, 2006 at 01:36 PM

    Yes, your best move on this is to use Postgresql.