开发者

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.
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜