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.
精彩评论