Michael Prasuhn wrote:
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.
Hmm... the auto-increment thing could be an issue. This leads me to two new questions:
1. Is it necessary for every row in the table to be unique? 2. Is it necessary for the primary key to be unique?
Currently, the table looks like this:
id, student_id, teacher_id, marks, date
The id is a primary key with auto_increment, but honestly I don't need it for anything. I normally search for student_id, so that's an index. Can I make it a primary key and drop the 'id' field? Then I won't have auto-increment and so I'll get non-blocking 50-INSERTs :-)
Would this work?
Please see http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some-pit... for some examples.
*Click*
Cheers, Daniel.