update t1 set c1 = ? where c2 = ?
create index i1 on t1(c2,c1)
Using a compound index to reduce lock timeouts with MySql 5.5
If you use MySql 5.5 and have a hot table where updates and inserts fail due to lock timeout then the following will be of interest. The SQL statement
Will (effectively) lock the WHOLE TABLE unless there is a compound index on the columns used in the update. Adding the index
will allow MySql's locking mechanism to only lock the records touched by the update and not the whole table.
