Not exists in a update statement takes more time

Question

In MySQL,

update table1 t1 
   set column1 = 100,
       column2 = 200
 where column3 > 500 
   and not exists(select * 
                    from table2 
                   where column1 = t1.column1);

This query is too time consuming to execute, do have any other faster way of rewriting.

for table2 ID is a primary column so i guess i can rewrite as

update table1 t1 
   set column1 = 100,
       column2 = 200
 where column3 > 500 
   and not exists(select ID 
                   from table2  
                  where column1 = t1.column1);

but still the query takes 2 secs to run, i want something in millisecs.

Answer 1

When using Not exists, as soon as the query processor finds a row, it can stop, but if the row truly does not exist, then it has to examine the entire table (for the columns defined in the subquery) before it knows for sure that the row does not exist... So the only way to speed it up is, if there are any filters in the subquery, to place an index or indices on those columns. In your case, that would mean an index on Table2.column1

Using a join instead is likely to not be a help, because whether it is a join or a not exists subquery, the query processor has to perform the same logical IOs to process the data.

To use a join, (i'm not sure about MySql syntax, it might be:P

UPDATE TABLE1 SET
     column1 = 100, 
     column2 = 200 
  From Table1 t1
      Left Join TABLE2 t2 
          ON t2.column1 = t1.column1 
  WHERE t2.column1 Is Null

or

  Update t1 Set
     column1 = 100, 
     column2 = 200 
  From Table1 t1
      Left Join Table2 t2 
          ON t2.column1 = t1.column1 
  Where t2.column1 Is Null

Answer 2

Try this (which does not use a correlated sub-query):

UPDATE Table1
   SET Column1 = 100, Column2 = 100
 WHERE Column3 > 500
   AND Column1 NOT IN (SELECT Column1 FROM Table2);

Answer 3

Try this instead:

   UPDATE TABLE1 
LEFT JOIN TABLE2 ON TABLE2.column1 = TABLE1.column1
                AND TABLE2.column2 IS NULL 
      SET column1 = 100,
          column2 = 200
    WHERE TABLE2.column1 IS NULL
      AND column3 > 500