1

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?

2 Answers 2

2

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);
5
  • Thanx man, but how is this one better?
    – AliBZ
    Dec 23, 2011 at 20:20
  • I get error with the second one: UPDATE user_activity ac INNER user_profile pr ON ac.user_profile1_id = pr.user_profile_id SET ac.user_profile1_id_int = pr.user_profile_id_int;
    – AliBZ
    Dec 23, 2011 at 20:27
  • I forgot the word JOIN after the word INNER. I just updated it. Try Again !!! Dec 23, 2011 at 20:30
  • Thanx, I ran the query. I will post the result ;)
    – AliBZ
    Dec 23, 2011 at 20:33
  • WOW ! I don't believe it ! 33 min 18 sec !!!! Thanx man ! Best DBA ever !
    – AliBZ
    Dec 23, 2011 at 21:05
1

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.

3
  • Thanx, But you know, I want to do this update and learn more about the queries. Because in the future I have to work with large datasets so I think its important for me to understand more about these.
    – AliBZ
    Dec 23, 2011 at 20:31
  • Some DML statements will take long(or comparatively long). If you update all records in 100GB table, you shouldn't expect it to happen in 1 second. In your case having an index on 2 user_profile fields (user_profile.user_profile_id, user_profile.user_profile_id_int) may improve your update query performance , but creating such an index may also take long time.
    – a1ex07
    Dec 23, 2011 at 20:38
  • I did the indexing before and it took long time. But the join solution which RolandoMySQLDBA said is great. It was done in 30 mins !
    – AliBZ
    Dec 23, 2011 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.