如果其他表中的数据不匹配，则更新表

Definition of both tables (it's just an example, I can't merge tables, no):

两个表的定义(这只是一个例子,我不能合并表,不):

CREATE TABLE `MyTable`(
  `id` int NOT NULL,
  `a` varchar(10) NOT NULL, 
  `b`  varchar(10) NOT NULL,
  `state` tinyint NOT NULL,
  PRIMARY KEY (`id`)
);

Goal: Update MyTable1 records that don't share any value with "a" OR "b" from MyTable2.

目标:使用MyTable2中的“a”或“b”更新不与任何值共享的MyTable1记录。

My solution:

update MyTable1 as t1 
  inner join MyTable2 as t2 on (t1.a != t2.a and t1.b != t2.b) 
set t1.state=3;

I'm basically joining tables where no columns match, so that I can update the state of such records.

我基本上加入没有列匹配的表,这样我就可以更新这些记录的状态。

My problem: This is slow. It took 6 seconds with 5000 entries in MyTable1 and 3000 entries in MyTable2.

我的问题:这很慢。 MyTable1中有5000个条目,MyTable2中有3000个条目需要6秒。

Question: Can it be any faster (if your solution goes a lot faster, I'll take it too ;)?

问题:它可以更快(如果你的解决方案变得更快,我也会接受它;)?

EDIT: My "solution" actually doesn't work at all.

编辑:我的“解决方案”实际上根本不起作用。

2 个解决方案

#1

Your join might find a ton of matches per row. That can make a join really expensive. Try a not exists instead:

您的加入可能会在每行中找到大量的匹配项。这可能会使加入真的很贵。尝试不存在而不是:

update  MyTable1 as t1
set     t1.state = 3
where   not exists
        (
        select  *
        from    MyTable2 as t2 
        where   t1.a = t2.a 
                or t1.b = t2.b
        )

or even a double subquery:

甚至是一个双子查询:

where   not exists
        (
        select  *
        from    MyTable2 as t2 
        where   t1.a = t2.a 
        )
        and not exists
        (
        select  *
        from    MyTable2 as t2 
        where   t1.b = t2.b
        )

#2

This might be faster:

这可能会更快:

UPDATE table1 t1 SET t1.state = 3
WHERE t1.id IN
  (SELECT s.id FROM 
     (SELECT T2.id FROM Table1 t2
      LEFT JOIN table2 t3 ON (t2.a = t3.a AND t2.b = t3.b)
      WHERE T3.ID IS NULL
     ) s
  )

#1