How to optimize MySQL UPDATE

mysql, query-optimization, sql

Solution

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;

Problem

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.

Original source