sql - Performant Update of multiple columns using random values from another table - Stack Overflow

admin管理员组

文章数量:1128329

I need to update multiple columns of a table t1 with random rows from another table t2. t1 has ~ 1 Million Rows, t2 has 50k

I had originally tried to use this

Update t1 SET 
field1=(select t2.field1 from t2 order by rand() limit 1),
field2=(select t2.field2 from t2 order by rand() limit 1)

+----+----------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+
| id | select_type     | table   | partitions | type | possible_keys | key   | key_len | ref | rows  | filtered | Extra              |
+----+----------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+
| 1 | UPDATE        | t1| NULL    | index | NULL     | PRIMARY | 4    | NULL | 932170|  100.00 | NULL              |
| 3 | UNCACHEABLE SUBQUERY | t2| NULL    | ALL  | NULL     | NULL  | NULL  | NULL |  49717 |  100.00 | Using temporary; Using filesort |
| 2 | UNCACHEABLE SUBQUERY | t2| NULL    | ALL  | NULL     | NULL  | NULL  | NULL |  49717|  100.00 | Using temporary; Using filesort |
+----+----------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+

But the performance was poor, I then tried

Update t1 SET
field1=(select t2.field1 from t2 where id=FLOOR(1+rand()*50000) limit 1),
field2=(select t2.field2 from t2 where id=FLOOR(1+rand()*50000) limit 1)
+----+----------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type     | table   | partitions | type | possible_keys | key   | key_len | ref | rows  | filtered | Extra    |
+----+----------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | UPDATE        | t1| NULL    | index | NULL     | PRIMARY | 4    | NULL | 932170|  100.00 | NULL    |
| 3 | UNCACHEABLE SUBQUERY | t2| NULL    | ALL  | NULL     | NULL  | NULL  | NULL |  49717|  10.00 | Using where |
| 2 | UNCACHEABLE SUBQUERY | t2| NULL    | ALL  | NULL     | NULL  | NULL  | NULL |  49717|  10.00 | Using where |
+----+----------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

But this is even slower, is there a better way to do this?

Edit: Table info and Plans

CREATE TABLE t1 (
id int,
field1 varchar(400),
field2 varchar(400)
PRIMARY KEY(id))

CREATE TABLE t2 (
    id int,
    field1 varchar(400),
    field2 varchar(400)
    PRIMARY KEY(id))

This post does not answer my question because it is not related to an update statement, and im not sure how to change it to work with an update

本文标签： sqlPerformant Update of multiple columns using random values from another tableStack Overflow