Execute MySQL update query on 750k rows

Question

I've added a field to a MySQL table. I need to populate the new column with the value from another table. Here is the query that I'd like to run:

UPDATE       table1 t1
SET          t1.user_id = 
                        (
                        SELECT   t2.user_id 
                        FROM     table2 t2 
                        WHERE    t2.usr_id = t1.usr_id
                        )

I ran that query locally on 239K rows and it took about 10 minutes. Before I do that on the live environment I wanted to ask if what I am doing looks ok i.e. does 10 minutes sound reasonable. Or should I do it another way, a php loop? a better query?

Answer 1

There are two rather important pieces of information missing:

1. What type of tables are they?
2. What indexes exist on them?

If table2 has an index that contains user_id and usr_id as the first two columns and table1 is indexed on user_id, it shouldn't be that bad.

Answer 2

Use an UPDATE JOIN! This will provide you a native inner join to update from, rather than run the subquery for every bloody row. It tends to be much faster.

update table1 t1
inner join table2 t2 on
    t1.usr_id = t2.usr_id
set t1.user_id = t2.user_id

Ensure that you have an index on each of the usr_id columns, too. That will speed things up quite a bit.

If you have some rows that don't match up, and you want to set t1.user_id = null, you will need to do a left join in lieu of an inner join. If the column is null already, and you're just looking to update it to the values in t2, use an inner join, since it's faster.

I should make mention, for posterity, that this is MySQL syntax only. The other RDBMS's have different ways of doing an update join.

Answer 3

You don't have an index on t2.usr_id.

Create this index and run your query again, or a multiple-table UPDATE proposed by @Eric (with LEFT JOIN, of course).

Note that MySQL lacks other JOIN methods than NESTED LOOPS, so it's index that matters, not the UPDATE syntax.

However, the multiple table UPDATE is more readable.