Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

2 Answers 2

up vote 2 down vote accepted

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);
share|improve this answer
    
Thanx man, but how is this one better? –  AliBZ Dec 23 '11 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 '11 at 20:27
    
I forgot the word JOIN after the word INNER. I just updated it. Try Again !!! –  RolandoMySQLDBA Dec 23 '11 at 20:30
    
Thanx, I ran the query. I will post the result ;) –  AliBZ Dec 23 '11 at 20:33
    
WOW ! I don't believe it ! 33 min 18 sec !!!! Thanx man ! Best DBA ever ! –  AliBZ Dec 23 '11 at 21:05

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.

share|improve this answer
    
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 '11 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 '11 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 '11 at 21:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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