How to enhance speed of UPDATE statement 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;
Change the temp table to:

CREATE TEMPORARY TABLE cons_temp (
    PRIMARY KEY(consultant)
    ) ENGINE=InnoDB
    SELECT DISTINCT consultant_id, consultant
          FROM testdata_biactivtity_rob;
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.