How to optimize a MySQL update which contains an "in" subquery?

Question

How do I optimize the following update because the sub-query is being executed for each row in table a?

update 
  a 
set 
  col = 1 
where 
  col_foreign_id not in (select col_foreign_id in b)

Answer 1

You could potentially use an outer join where there are no matching records instead of your not in:

update table1 a
    left join table2 b on a.col_foreign_id = b.col_foreign_id
set a.col = 1
where b.col_foreign_id is null

This should use a simple select type rather than a dependent subquery.

Your current query (or the one that actually works since the example in the OP doesn't look like it would) is potentially dangerous in that a NULL in b.col_foreign_id would cause nothing to match, and you'd update no rows.

not exists would also be something to look at if you want to replace not in.

I can't tell you that this will make your query any faster, but there is some good info here. You'll have to test in your environment.

Here's a SQL Fiddle illuminating the differences between in, exists, and outer join (check the rows returned, null handling, and execution plans).