HN Explorer

Ask HN: InnoDb vs MyISAM?

2 points by mr_twj ↗ HN
I'm a newcomer here so I apologize if this topic has been beaten to death and then some; any useful links would be appreciated. I've been using MyISAM for a while but now have seen the light of InnoDB. I've read a little that it's slow and doesn't play well with third party technology just because it's newer. I'm just wondering if there are any drawbacks to InnoDB encountered firsthand in the trenches (for your basic, run-of-the-mill applications) before I decide to switch to this engine.

The prospect of transactions make me drool feverishly for InnoDB. I hope I'm not alone.

Thanks.

5 comments

[ 3.1 ms ] story [ 23.1 ms ] thread
roobaron
http://www.google.com.au/search?hl=en&source=hp&q=in...

In a nutshell, it depends on what you are using the database for, you may even consider something outside of a relational database if you don't need SQL and relational structure to your data. The biggest difference is the lack of granular transactions (MyISAM). This will kill your application if you have concurrent writes to a MyISAM table.

We have clients who mix MyISAM and innodb, in their case, they need to store lots of data but want it as small as possible, but still able to be accessed via SQL queries. So it is MyISAM for all tables which are read only and Innodb for the current data which needs concurrency for updates.

mr_twj
Thanks. I do a lot of inserts and updates, so yeah, I'm really feeling the burn right now.
byoung2
If you need to do fulltext searches, MyISAM is your only choice. If you switch to InnoDB, you might have to split some tables and rewrite some code to use both engines.
matthijs
Note that the full-text modes only support prefix searching. For postfix you could either reverse the field your looking for (redundant data in your table), or use something like sphinx; which nowadays has great integration with MySQL.
nostrademons
Usually you want to default to InnoDB unless you know for sure that the table's usage pattern fits MyISAM's strengths. Those include read-only tables, read-infrequently tables (like configuration data), and logging tables, where you're appending a bunch of rows at once and rarely (if ever) need to mutate existing data.

If you want full-text search, you're usually better off with something like Solr/Lucene or Google Custom Search Engines than MyISAM's built-in full text indexing.

The problem with MyISAM is that if you ever get significant write-load, the table-level locking will kill you. InnoDB uses row-level locking, which scales much better under heavy writes. Writes are far more likely to be a bottleneck than reads, so the faster read speed of MyISAM is rarely a boon in practice.