Ask HN: InnoDb vs MyISAM?
3 comments
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.
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.
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.
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.
Thanks. I do a lot of inserts and updates, so yeah, I'm really feeling the burn right now.
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.
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.
The prospect of transactions make me drool feverishly for InnoDB. I hope I'm not alone.
Thanks.