CODEWITHSUNDEEP
mysqldatabasesql-updatelarge-data
ghosh'.
ghosh'.

Reputation: 1607

MySQL Update a Complete Column data from Another Table with same Primary Key (30 Million Row)

I have two tables: Final and Primary in my MySQL database. Final Table:

UID (Primary Key), Surface (Varchar), BCD_Value (Int , Index), Env_Value (Int)

Primary Table:

UID (Primary Key), BCD_Value (Int , Index)

There are 30 Million rows in each table. Primary Table has the correct BCD_Value. I have to update Final Table's BCD_Value with values from Primary on matching UID.

I was using following in MySQL command line

update Primary pri, Final fin set fn.BCD_Value = pri.BCD_Value where fin.UID = pri.UID

But it does nothing after 36 hours.

Further, I have implemented a code in perl which creates update statement for 5000 rows at time (loops it for 30 million/ 5000 times). Now it is working but I desperately need to fasten it.

Upvotes: 0

Views: 628

Answers (1)

Skinny Pipes
Skinny Pipes

Reputation: 1035

try converting into ANSI SQL-92 syntax

UPDATE Final fin INNER JOIN `Primary` pri
       ON fin.UID = pri.UID
SET fn.BCD_Value = pri.BCD_Value

it takes time to update all records because it updates the indexes as well.

try removing the index on Final.BCD_Value first then add again after the update has been made.

Upvotes: 1

Related Questions