CODEWITHSUNDEEP
mysql
Anthony Vipond
Anthony Vipond

Reputation: 1957

Join and update large table in Mysql

Table one has:

id, new_id, col1, col2

Table two has:

id, col1, col2

Table one is over a million rows and table two is about 250K.

What I want to do in a single query is:

SET   t1.new_id = t2.id 
WHERE t1.col1   = t2.col1
AND   t1.col2   = t2.col2
AND   t1.new_id IS NULL

What I have right now is:

UPDATE t1
LEFT JOIN t2
ON  t1.new_id IS NULL
AND t1.col1 = t2.col1
AND t1.col2 = t2.col2
SET t1.new_id = t2.id

I think it can't be right. It's so slow it's never finishing... thanks in advance!

Upvotes: 0

Views: 887

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The syntax of your query is fine. Except, the filter on t1 should go in the where clause:

UPDATE t1 LEFT JOIN
       t2
       ON  t1.col1 = t2.col1 AND t1.col2 = t2.col2
SET t1.new_id = t2.id
WHERE t1.new_id IS NULL

Or, use an inner join:

UPDATE t1 JOIN
       t2
       ON t1.new_id IS NULL AND  t1.col1 = t2.col1 AND t1.col2 = t2.col2
SET t1.new_id = t2.id;

The LEFT JOIN keeps all rows in the first table regardless of the ON clause. That means that your formulation is updating all the rows in t1, which may account for the performance problem. Either using a where clause for filters on the first table or using an inner join should fix this aspect of the problem.

In addition, indexes would help. I would recommend the following: t2(col1, col2, id), t1(new_id, col1, col2).

Upvotes: 2

Related Questions