0

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?

3
  • Why not 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? What reason makes you to use "batches"?
    – Akina
    Feb 9, 2020 at 15:09
  • too many rows for one update, also I need a sense of progress to show to the customer
    – omri
    Feb 9, 2020 at 15:43
  • Have you tried using a temporary table with a JOIN, instead of IN? Feb 9, 2020 at 17:33

2 Answers 2

0

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

3
  • not working, https://dev.mysql.com/doc/refman/8.0/en/update.html : For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.
    – omri
    Feb 9, 2020 at 16:38
  • this is not good, you are assuming new_user_id is unique, but new_user_id can be the old_user_id of another row.
    – omri
    Feb 10, 2020 at 8:49
  • @omri You're right, and I understand this. Globally the solution must include starting query with CTE over users_ids_mapping which will clear such chain replaces with one final replace by joining the whole chain into one record. And check that multiple records with the same current_user_id not exists. And check that there is no record in users_ids_mapping which will cause duplicates by user_id in remote_users...
    – Akina
    Feb 10, 2020 at 8:55
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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