InnoDB can even perform better then MyISAM in high read environment if you have a good ratio of data in memory (i.e. innodb_buffer_pool_size).
Even without FK and RI and transactions, InnoDB can help with table locking especially given your number of tables, and description, you probably have many table joins, even one long query can kill performance due to table locking. A transactional engine (InnoDB is just one of them is a better recommendation) The most common problem is people don't tune InnoDB with basic settings, so they feel it runs worse, when 5 correct settings can make all the difference. Ronald 01110010 01101111 01101110 01100001 01101100 01100100 00100000 01100010 01110010 01100001 01100100 01100110 01101111 01110010 01100100 *Ronald Bradford *MySQL Expert specializing in Performance Tuning, Scalability and High Availability Oracle ACE Director<http://apex.oracle.com/pls/otn/f?p=19297:4:134763154226248::NO:4:P4_ID:1820> MySQL Community Member of the Year 2009<http://www.mysql.com/why-mysql/awards/community-2009.html> Co author of Expert PHP and MySQL. http://expertphpandmysql.com Web Site: http://ronaldbradford.com Linked In: http://www.linkedin.com/in/ronaldbradford Twitter: @RonaldBradford <http://twitter.com/ronaldbradford>, @MySQLExpert <http://twitter.com/MySQLExpert> Skype: RonaldBradford On Wed, Jan 5, 2011 at 2:48 PM, Hans Zaunere <[email protected]> wrote: > > If you have 20 data tables with a lot of relationships among them, do > > you think that is reason enough to choose InnoDB -- with its support for > > transactions and foreign keys -- over MyIsam? > > If you need transactions and foreign keys, then yes. > > > I'm working on an app and taking great pains to preserve integrity at > > the application level and coming to realize that it's even harder than I > > Don't - it's not really possible, and if you truly need that type of thing, > it has to be done in the database anyway. > > > thought. Say you have a form with a dropdown populated by some data from > > table x. I validate the input to make sure the user isn't maliciously > > choosing a value of their own choosing rather than my select menu. Fine, > > but in the meantime some other user came long and (legitimately) deleted > > from table x the row whose id the first user is submitting. So after > > validating, if using MyIsam, I would have to lock tables and a run > > sanity-check SELECT query to make sure the row in x still exists. This > > is gonna be happening rather a lot. I think those extra queries will > > offset some of the performance advantage of MyIsam over InnoDb. > > Yes, it gets ridiculous. And in some cases, like these, InnoDB will be > faster anyway. > > > Also, in my case, it's unlikely that I will ever see much of a server > > load. A few people will be admins doing reads and writes, a few more > > people -- like maybe 100 or 200 -- might be mostly reading > > intermittently over the course of a typical day. > > Go for InnoDB if you need that type of functionality - the performance > won't > make a difference with that type of traffic. > > H > > > _______________________________________________ > New York PHP Community MySQL SIG > http://lists.nyphp.org/mailman/listinfo/mysql > > NYPHPCon 2006 Presentations Online > http://www.nyphpcon.com > > Show Your Participation in New York PHP > http://www.nyphp.org/show_participation.php >
_______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
