Fast Cross Table Update with MySQL

Simple question :D. I know how to do it, but I have开发者_运维百科 to do it fast.

What’s the most time efficient method?

Scenario: two tables, tableA and tableB, update tableA.columnA from tableB.columnB, based on tableA.primarykey = tableB.primarykey.

Problem: tableA and tableB are over 10.000.000 records each.

---

update TableA as a
    join TableB as b on
        a.PrimaryKey = b.PrimaryKey
set a.ColumnA = b.ColumnB

Updating 10 million rows cannot be fast. Well... at least in comparison to the update of one row.

The best you can do:

• indexes on joining fields, but you've got this, as these fields are primary keys
• limit by where condition if applicable. Index covering where condition is needed to speed it up.