Update Mysql Table In Chunks

I am trying to update a MySQL InnoDB table with c. 100 million rows. The query takes close to an hour, which is not a problem. However, I'd like to split this update into smaller c

Solution 1:

I ended up with the procedure listed below. It works but I am not sure whether it is efficient with all the queries to identify consecutive ranges. It can be called with the following arguments (example):

call chunkUpdate('SET var=0','someTable','theKey',500000);

Basically, the first argument is the update command (e.g. something like "set x = ..."), followed by the mysql table name, followed by a numeric (integer) key that has to be unique, followed by the size of the chunks to be processed. The key should have an index for reasonable performance. The "n" variable and the "select" statements in the code below can be removed and are only for debugging.

delimiter //CREATEPROCEDURE chunkUpdate (IN cmd VARCHAR(255), IN tab VARCHAR(255), IN ky VARCHAR(255),IN sz INT)
BEGINSET@sqlgetmin= CONCAT("SELECT MIN(",ky,")-1 INTO @minkey FROM ",tab); 
  SET@sqlgetmax= CONCAT("SELECT MAX(",ky,") INTO @maxkey FROM ( SELECT ",ky," FROM ",tab," WHERE ",ky,">@minkey ORDER BY ",ky," LIMIT ",sz,") AS TMP"); 
  SET@sqlstatement= CONCAT("UPDATE ",tab," ",cmd," WHERE ",ky,">@minkey AND ",ky,"<=@maxkey");
  SET@n=1;

  PREPARE getmin from@sqlgetmin;
  PREPARE getmax from@sqlgetmax;
  PREPARE statement from@sqlstatement;

  EXECUTE getmin;

  REPEAT
    EXECUTE getmax; 
    SELECT cmd,@nAS step, @minkeyAS min, @maxkeyAS max;
    EXECUTE statement;
    set@minkey=@maxkey;
    set@n=@n+1;
  UNTIL @maxkeyISNULLEND REPEAT; 
  select CONCAT(cmd, " EXECUTED IN ",@n," STEPS") AS MESSAGE;
END//