Storage entgine choice for a website - Myisam or Innodb?

What would be an obvious choice as a storage engine for a Craiglist type site - MyIsam or InnoDb and Why?

Please explain with specific reasons for your preference

A Craigslist type of site would have many reads as well as writes but probably more reads since lots of people came there looking for the info they wanted(through search engines)and do not have much to post ... but just f开发者_StackOverflow社区ound what they were looking for...

What would you do in such a case Innodb or Myisam ? Since there are both reads and writes..?

In v.high level terms:

InnoDb

• supports transactions
• row level locking
• supports foreign key constraints
• good handling for workloads with lots of concurrent writes
• crash safe
• row level locking avoids lots of nasty locking situations

MySQL

• No transactions
• Table level locking
• supports MERGE tables which can be useful
• Good for workloads where there are lots of reads and not so many writes
• Not crash safe
• Table level locking can cause nasty situations when slow/poor queries are run

For any new project I work on now I would almost definitely choose InnoDb. There's less chance of your db crashing, you can take advantage of transactions and assuming you've got a decent chunk of RAM on the server you're hosting it on (RAM is so cheap these days) then you should get really good performance out of it. InnoDb is more complicated in terms of performance tuning but with luck it'll just work happily for you out of the box.

I prefer InnoDB because it is more of a "real" database. This link provides more detail:

http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM

Choice quote:

"InnoDB is transaction-safe meaning data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-locking, meaning while one query is busy updating or inserting a row, another query can update a different row at the same time. These features increase multi-user concurrency and performance."

Most of the features of InnoDB come down to data integrity, always a good thing :)

In short, and to summarize : * MyISAM if you're looking for performance. * InnoDB if you're looking for robustness (Transactions, foreign keys).

I would always favor robustness over performance, even more so at the beginning of a project. So start with InnoDB. Once your website is successful enough, you might consider MyISAM.