

Ask HN: InnoDb vs MyISAM? - mr_twj

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.<p>The prospect of transactions make me drool feverishly for InnoDB. I hope I'm not alone.<p>Thanks.
======
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.

------
roobaron
[http://www.google.com.au/search?hl=en&source=hp&q=in...](http://www.google.com.au/search?hl=en&source=hp&q=innodb+vs+MyISAM&btnG=Google+Search)

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.

