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?
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"?IN
?