[SOLVED] How to avoid full table scan when update with IN clause in MySQL

How to avoid full table scan when update with IN clause in MySQL

I got 2 MySQL tables: termlist and blacklist. They both have index on field 'term' and blacklist has another index on field 'status'.

I want to update status of terms in termlist, that also appear in blacklist with status as 'A', to 'B', I issue this SQL statement:

update termlist set status = 'B' where term in (select term from blacklist where status = 'A')

It cause a full table scan on termlist. I want to use 'update with inner join' but I can't since there's a where clause in select statement.

I know I can create a temp table from that select statement and then update inner join with that temp table but this is kinda tedious if I want to do this update many times.

Is there one single update statement that can do the work without full table scan?

Solution

You may use:

update termlist t inner join blacklist b 
    on t.term=b.term
    set t.status = 'B' 
    where b.status = 'A'