Geek asks and answers

How to optimize MySQL UPDATE

Is there a way to optimize this update request in MySql?

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

Both tables have about 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 your help.

+5
source share
3 answers

here's how i do it:

create a temporary table to store aggregated values

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

add an index to the temporary table for quick connection to table 1 (you can omit this step depending on the size of the data)

ALTER TABLE tmp_operation ADD UNIQUE INDEX (id);

update with a simple connection. you can use left or inner join depending on whether you want to update columns to zeros)

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

delete temporary table after execution

DROP TABLE tmp_operation;
+4
source

t2, JOIN ( @frail, ):

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 ;

table2, (id, column) .

+2

Add the forign key to table2 of the primary key of table1.

UPDATE table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id
SET t1.column = t2.column
having MIN(t2.column)

examples

0
source

More articles:


All Articles