how to boost a slow update query

Guys,
I have 2 tables, table1 (id, val1, val2,val3) and table2 (id, val1_new), each with exactly the same number of rows (~200,000) and same values in id columns. I want to update a part of table1 which has val2=val3 (~ 300 rows), replace val1 of table1 with val1_new of table2. Both tables have indices on id.
I use this query:
update table1 a set a.val1 =
(select val1_new from table2 b where a.id=b.id)
where a.val2=b.val3;

it runs in about 3 mins just to update 300 rows, while a query with the same purpose in mysql runs in 10sec

I also run a slightly different version:

update table1 a set a.val1 =
(select val1_new from table2 b where a.id=b.id and a.val2=a.val3);

but same performance

any suggestion for improvement?

Thanks,
Tam Received on Fri Apr 30 2004 - 13:25:28 CDT