I recently helped one customer to help resolve performance issue. The problem was caused by a very simple update query that modifies only one record:
Ideally it should be using "unique key" to examine/update 1 row. Table in question has got < 200 rows. However, it still does not look too bad "6 rows vs 1 rows" but it blocked large number of similar update queries due to long running transaction. The first thing done was to identify and kill long running transaction that was blocking other 'update' queries. and then tune 'update' query.
The issue here is that value 03 cannot be compared to column NUM_MODULE without data type conversion i.e. we are comparing numeric value with string column, that is why MySQL could not use available and most suitable index . We had two solutions to fix this:
a) Modify query to use quotes with a value e.g.
b) Modify table structure to use type integer
NUM_MODULE SMALLINT NOT NULL
The easy fix was to modify table structure, because all NUM_MODULE values are numeric and table is quite small.