How to enhance speed of UPDATE statement

Question

Out of practice , some tips on indexes efficiency appreciated. Creating test data ... changing consultant name on a large table using an update statement. one of my tables has 10 rows, second table has 4500 rows ...

Tips appreciated to reduce the process time...

currently getting this error message which I presume is a timeout ...

Lookup Error - MySQL Database Error: Lock wait timeout exceeded; try restarting transaction

---

drop table if exists cons_temp;

create temporary table cons_temp
as select distinct consultant_id, consultant
from testdata_biactivity_rob;

update testdata_biactivityx a
inner join cons_temp b on b.consultant_id = a.consultant_id
set a.consultant = b.consultant;

Answer 1

1. Change the temp table to:

   CREATE TEMPORARY TABLE cons_temp ( PRIMARY KEY(consultant) ) ENGINE=InnoDB SELECT DISTINCT consultant_id, consultant FROM testdata_biactivtity_rob;

2. There is no need for the create table to be in the transaction, so start with

   SET autocommit=ON;

This will isolate the "wait timeout" to the UPDATE.