Update Large Table

Question

I have these two tables:

user_profile:
user_profile_id_int int primary key 
user_profile_id varchar

user_activity:
user_profile_id_int int key
user_profile_id varchar

both fields in user_profile are ok but I don't have the user_profile_id_int from user_activity and I want to update it using the map I have in user_profile. I wrote a query like this:

update user_activity a
set user_profile_id_int =
(select user_profile_id_int from user_profile b
where a.user_profile_id = b.user_profile_id);

It was running on a really strong server for 1 day and I stopped it. But to record has been updated.

So 2 questions here:

1. Is there a better and faster way to do this?
2. Is this way ok?

Answer 1

Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id_int);

Answer 2

I think you can try adding LIMIT to your update (and modify a bit WHERE to filter out already changed records). If you do, for instance, LIMIT 100, it won't take 1 day. Surely, if you want to change all the records, you will have to write a script that executes UPDATE until number of updated rows is 0, but it won't block the whole table.