mysql - 从另一个表更新新列 : too slow

Question

我有一个很大的表，我决定通过从另一个表中引入一个带有 ID 的新列并删除另外两个列来减小它的大小。以下是表格:

Table tests:
+---------------+----------------------+------+-----+---------+----------------+
| Field         | Type                 | Null | Key | Default | Extra          |
+---------------+----------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned  | NO   | PRI | NULL    | auto_increment |
| ml            | varchar(16)          | NO   |     | NULL    |                |
| test_num      | smallint(5) unsigned | NO   |     | NULL    |                |
........                                                                                                                   

Table data:
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned  | NO   | PRI | NULL    | auto_increment |
| test_id   | bigint(20) unsigned  | NO   |     | NULL    |                |                                                                                                                   
| ml        | varchar(16)          | NO   |     | NULL    |                |                                                                                                                   
| test_num  | smallint(5) unsigned | NO   |     | NULL    |                |                                                                                                                   
........

所以我在表 data 中添加了新列 test_id 并希望用 tests 中的 id 填充它然后删除列 ml 和 test_num。

我已经尝试使用 join 来做到这一点:

update data join tests 
    on data.ml=tests.ml 
    and data.test_num=tests.test_num 
    set test_id=tests.id;

这个查询工作了三个小时，所以我取消了它并尝试了另一个:

update data 
    set test_id=
    (select id from tests 
    where data.ml=tests.ml 
    and data.test_num=tests.test_num);

表演了四个小时，不知道能不能在可预见的时间内结束。我的问题是:这些查询是否正确，哪个更有效，还有其他方法可以满足我的需要吗？

Answer 1

您的查询速度很慢，因为您在连接中使用的列上没有索引，假设您有大量数据。

Alter Table tests ADD INDEX idx_ml(ml),ADD INDEX idx_test_num(test_num);
Alter Table data ADD INDEX idx_ml(ml),ADD INDEX idx_test_num(test_num);

尝试添加索引，因为它还会减少您 future 查询的执行时间。