Updating table with many rows

Question

I have 2 tables:

CREATE TABLE `users_ids_mapping` (
 `current_user_id` bigint(20) NOT NULL,
 `new_user_id` bigint(20) NOT NULL AUTO_INCREMENT,
 UNIQUE KEY `current_user_id` (`current_user_id`),
 KEY `new_user_id` (`new_user_id`)
) ENGINE=INNODB 

CREATE TABLE `remote_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL DEFAULT -1,
    ..
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
) ENGINE=InnoDB 

The first table holds old user_id values and new ones, what I want to do is to update the second table remote_users user_id with the new values.

I do it by running 2 queries:

SELECT id FROM remote_users
ORDER BY id ASC 
LIMIT {limit}
OFFSET {offset}

And when having the next ids, I run:

UPDATE remote_users r
JOIN users_ids_mapping m ON m.current_user_id = r.user_id
SET r.user_id = m.new_user_id
WHERE r.id IN({string.Join(",", ids)})

users_ids_mapping can reach to around 100k rows.

I am using batches of 10k rows. This worked for costumers with remote_users table of 10-100m rows, but now i ran it on a costumer with around 200m+ rows and its simply takes too much time.

I can see that in the beginning its pretty fast:

running batch number:1 with offset:10000 found:10000 rows, took:76ms

But when the offset is high it takes almost 3 mins to update 10k rows:

running batch number:13678 with offset:136780000 found:10000 rows, took:185949ms

Any suggestions on how I can speed up the process?

Answer 1

too many rows for one update

If so use simple

UPDATE users_ids_mapping m, 
       remote_users u
SET u.user_id = m.new_user_id
WHERE u.user_id = m.current_user_id
LIMIT 1000

Replace 1000 with a chunk size which is safe for you - both for resource consumption and for progress displaying.

Repeat until "Affected rows = 0". Set approximate upper limit of the progressbar with = 1 + (count of records in users_ids_mapping) / (chunk size)

---

UPDATE

For multiple-table syntax, ORDER BY and LIMIT cannot be used.

UPDATE remote_users u
SET user_id = ( SELECT new_user_id
                  FROM users_ids_mapping
                  WHERE u.user_id = current_user_id )
WHERE EXISTS ( SELECT new_user_id
               FROM users_ids_mapping
               WHERE u.user_id = current_user_id )
LIMIT ??? ;

fiddle

Answer 2

There are many reasons why a UPDATEing a huge number of rows will take an unreasonable length of time. (Ditto for DELETE.)

If most of the table is being changed, then probably the fastest way is to create a new table with the 'correct' values. Then use RENAME TABLE to swap the new table into place.

Here are several tips on doing 'big' deletes or updates: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks