CODEWITHSUNDEEP
mysql
Leo Stein
Leo Stein

Reputation: 159

Mysql update query over 10 million records

I've got to update a field in a table (250 000 records) with the value of a field from another table (10 000 000 records) based on the email...

I've tried: UPDATE table1 t1, table2 t2 SET t1.country = t2.country WHERE t1.email = t2.email

But I got a "Query is being executed" forever.

What query should I use?

Thanks

Upvotes: 3

Views: 1477

Answers (2)

Chuck Callebs
Chuck Callebs

Reputation: 16441

This would be a good opportunity to employ a JOIN.

UPDATE table1 as t1
JOIN table2 t2 ON t1.email = t2.email
SET t1.country = t2.country

It will still take a while for your query to process, but it should reduce the time by a significant amount.

Upvotes: 2

Steffen Macke
Steffen Macke

Reputation: 1146

I don't see an obvious error in your query (and the database would yield an error in that case). So the problem we're looking at is to speed up the execution of your update. Is one of your two email fields indexed? If not, could you add an index and try again? E.g. ALTER TABLE table2 ADD INDEX(email)

Upvotes: 0

Related Questions