MyISAM or InnoDB

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?

6 comments

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.
Read more
Read less
  Cancel
Thijs van der Vossen

We always use InnoDB, except for when we need full-text indexes.
Read more
Read less
  Cancel
In my experience MyISAM is the primary reason people make fun of MySQL, for what it's worth.
Read more
Read less
  Cancel
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.
Read more
Read less
  Cancel
Sean Treadway

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.
Read more
Read less
  Cancel
random@striker.com

Yes, your best move on this is to use Postgresql.
Read more
Read less
  Cancel

Leave a comment