查询用数百万行填充MySQL数据库

I have two tables. The first one is named "weights" and contains around 250.000 rows:

我有两张桌子。第一个名为“权重”,包含大约250,000行:

mid / weight

中/重

1 / 3

1/3

2 / 12

2/12

3 / 7

3/7

The second one is called "recipient" and has around 2 million rows and is this way:

第二个被称为“收件人”,有大约200万行,就是这样:

mid / weight

中/重

1 / -

2 / -

I am trying to populate it with the "weight" information from the table "weights" and I am using this query:

我试图用“权重”表中的“权重”信息填充它,我正在使用此查询:

UPDATE recipient A
SET weight =
  (SELECT weight
   FROM recipient B
   WHERE B.mid = A.mid)

The query seems to work for the first few rows, but it seems that 2 million is too much. I am running MAMP on my mac. How would you advise me to do?

该查询似乎适用于前几行,但似乎200万太多了。我在我的Mac上运行MAMP。你怎么建议我做的?

1 个解决方案

I do suspect that you have no indexes defined on your table.

我怀疑你的桌子上没有定义索引。

UPDATE  recipient A
        INNER JOIN weight B
            ON B.mid = A.mid
SET     a.weight = b.weight

for faster performance, make weight.mid primary key and reference recipient.mid on it.

要获得更快的性能,请在其上创建weight.mid主键和引用recipient.mid。

I do suspect that you have no indexes defined on your table.

我怀疑你的桌子上没有定义索引。

UPDATE  recipient A
        INNER JOIN weight B
            ON B.mid = A.mid
SET     a.weight = b.weight

for faster performance, make weight.mid primary key and reference recipient.mid on it.

要获得更快的性能,请在其上创建weight.mid主键和引用recipient.mid。