[support] MyISAM vs InnoDB

[Michael Prasuhn]

On Mar 21, 2009, at 1:32 PM, Daniel Carrera wrote:
> I just wrote another post that explains my hosting environment. I am  
> interested in speed and reliability (isn't everybody?). My  
> understanding is that:
>
> 1) InnoDB is more reliable.
> 2) InnoDB reads faster, if the primary keys and indexes are set right.
> 3) InnoDB writes slower.
> 4) InnoDB requires more disk space and more RAM.

A lot of confusion here, from others, not just Daniel. Overall the  
biggest benefit is that InnoDB supports row-level locking versus table- 
locking on writes. For high traffic sites this can make some obvious  
benefits for tables like access logs, sessions, users, comments,  
node_comment_statistics and the like.

The downside is that for reading large amounts of simple data, InnoDB  
is slower than MyISAM. In fact MyISAM is probably one of the fastest  
engines out there in terms of reading data.

In short, unless you know you have large waits doing common tasks such  
as insert comments when the load on the site is high, or you have a  
way to examine how many connections are waiting on table locks before  
completion, you are probably best off sticking with MyISAM.

Also, while InnoDB is widely available these days, you may still run  
into hosts that don't support it, but do support MyISAM.


-Mike
__________________
Michael Prasuhn
503.488.5433 office
714.356.0168 cell
503.661.7574 home
mike at mikeyp.net
http://mikeyp.net