mysql - Updating 10 million rows while having a check with another table -

there 12 million rows in table , 10 million in table b. both these table have common field, user_id.

now i've added column in table add primary key of b.

so tables this

table +-------------+--------------+------+-----+---------+----------------+ | field       | type         | null | key | default |          | +-------------+--------------+------+-----+---------+----------------+ | id          | int(11)      | no   | pri | null    | auto_increment | | user_id     | int(11)      | no   |     | null    |                | | b_id        | int (11)     | yes  | mul | null    |                | +-------------+--------------+------+-----+---------+----------------+  table b +-------------+--------------+------+-----+---------+----------------+ | field       | type         | null | key | default |          | +-------------+--------------+------+-----+---------+----------------+ | id          | int(11)      | no   | pri | null    | auto_increment | | user_id     | int(11)      | no   |     | null    |                | +-------------+--------------+------+-----+---------+----------------+ 

now want update b_id in table a. in order that, had written following query:

update set a.b_id = (select b.id b , a.user_id = b.user_id ); 

but after indexing , doing in chunks of 100k, taking long time(around 3 min each).

is there better , faster way update it?

update set a.b_id = b.id table inner join table b on a.user_id = b.user_id 

make sure have indexes setup on user_id columns.

can't comment yet: joins historically faster in mysql long there isn't duplicated data.