我想弄清楚为什么我们之一
迁移脚本永远在我们正在尝试进行更新
从另一个表连接以获取相关的数据.
每个表(A,B)有大约100,000行.
# now populate the ACHIEVEMENT_INSTANCE.OBJECTIVE_INSTANCE_ID
update A a, B b
set a.INSTANCE_ID = b.INSTANCE_ID
where a.ID = b.ID;
看起来我们正在处理创建一些的INNER JOIN
笛卡尔积的类型在2表100,000 100,000之间
正在采取永远(可能很长时间).
根据MySQL更新默认使用内部联接不确定我们是否
可以使用一些其他类型的JOIN,它们不会那么糟糕.
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
The table_references clause lists the
tables involved in the join. Its
syntax is described in Section
12.2.8.1, “JOIN Syntax”. Here is an example: UPDATE items,month SET
items.price=month.price WHERE
items.id=month.id; The preceding
example shows an inner join that uses
the comma operator, but multiple-table
UPDATE statements can use any type of
join allowed in SELECT statements,
such as LEFT JOIN.