0

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 found what they were looking for...

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

user481913
  • 1,022
  • 1
  • 13
  • 36
  • This question is off-topic for Stack Overflow. I would recommend moving it to [Webmasters](http://webmasters.stackexchange.com/). See [Stack Overflow's FAQ](http://stackoverflow.com/faq) for more information about what is on-topic here. – the Tin Man Apr 26 '11 at 19:30
  • possible duplicate of [What's the difference between MyISAM and InnoDB?](http://stackoverflow.com/questions/12614541/whats-the-difference-between-myisam-and-innodb) – Kzqai Jan 12 '15 at 03:30

3 Answers3

4

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.

James C
  • 14,047
  • 1
  • 34
  • 43
  • I edited my question .. lease answer the specifics on read and write... As they say MyIsam performs better for reads... – user481913 Apr 26 '11 at 20:30
  • I would still run with InnoDb and if in the future you want to increase the performance further for the read-only side of things implement some kind of caching layer at either template or data layer for the ads displayed. – James C Apr 26 '11 at 20:34
3

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 :)

Ken Downs
  • 4,707
  • 1
  • 22
  • 20
0

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.

Ecco
  • 1,323
  • 1
  • 11
  • 15
  • 2
    If anyone is interesting in some real numbers of performance between MyISAM and InnoDB: http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/. In other words MyISAM isn't always faster, in fact InnoDB beats MyISAM in a lot of areas. – rzetterberg Apr 26 '11 at 19:34