[support] MyISAM vs InnoDB

[Michael Prasuhn]

On Mar 23, 2009, at 10:30 AM, Daniel Carrera wrote:
> Could you confirm that row-level locking means that you can do  
> multiple INSERTs at the same time?
>
> Suppose that you are going to do 50 INSERTs in one transaction (my  
> non-Drupal site does this at one place). Can two users do the 50- 
> INSERT operation at the same time?  (the application is a markbook/ 
> gradebook - the inserts are the marks/grades for 50 students).


Well there are some gotcha conditions, if you have a WHERE clause in  
your update, it must be using an index, otherwise it has to do a table  
scan, and locks the whole table. Also, any inserts using an auto- 
increment column will lock the entire table to prevent duplicate values.

> I thought that InnoDB was supposed to be faster for reading. At  
> least, for reading rows that are related by index (e.g. users 1-20  
> assuming that "user id" is an index).


Please see http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some-pitfalls.html 
  for some examples.

-Mike

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