How to optimize MySQL UPDATE

Question

Please is there any way how to optimize this update query in MySql?

   UPDATE table1 t1 
   SET t1.column = 
   (SELECT MIN(t2.column) 
     FROM table2 t2 WHERE t1.id = t2.id
    );

Both tables have around 250 000 records.

Table structure:

CREATE TABLE `table1` (
 `id` int(11) NOT NULL,  
 `column` datetime NOT NULL,
 PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `table2` (
 `code` int(11) NOT NULL,  
 `id` int(11) NOT NULL,    
 `column` datetime NOT NULL,
 PRIMARY KEY (`code, `id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ALTER TABLE table2 ADD CONSTRAINT FK_id 
    FOREIGN KEY (id) REFERENCES table1 (id)         
;

Thank you for help.

Answer 1

this is how I would do it :

create a temporary table to hold aggregated values

CREATE TEMPORARY TABLE tmp_operation 
SELECT id, MIN(`column`) as cln FROM table2 GROUP BY id;

add index to temporary table for faster join to table 1 (can omit this step depending on data size)

ALTER TABLE tmp_operation ADD UNIQUE INDEX (id);

update with simple join. you can use left or inner join depending if you want to update columns to nulls)

UPDATE table1 
SET table1.`column` = tmp_operation.cln
INNER JOIN tmp_operation ON table1.id = tmp_operation.id;

drop temporary table after done

DROP TABLE tmp_operation;

Answer 2

You could do it by first grouping table t2 and then use JOIN (this is similar to @frail's answer but without the temporary table):

UPDATE 
      table1 t1 
  JOIN
      ( SELECT id
             , MIN(column) AS min_column
        FROM table2 
        GROUP BY id
      ) AS t2
    ON t2.id = t1.id
SET t1.column = t2.min_column ;

An index at table2, on (id, column) would help performance.