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;
  • 1
    Give us: Your system's RAM, CPU, HDD! Give us the output of SHOW CREATE TABLE my_tables\G for all tables + record count per table. It beggars belief that an operation this small should timeout! What OS are you on? Is your disk (nearly) full?
    – Vérace
    Oct 20 '17 at 7:55
  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.