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

update t1 set c1 = ? where c2 = ?
Will (effectively) lock the WHOLE TABLE unless there is a compound index on the columns used in the update. Adding the index
create index i1 on t1(c2,c1)
will allow MySql's locking mechanism to only lock the records touched by the update and not the whole table.

No comments: