Update index values extremely slow on MySQL

Question

I have three tables, one is in database db1 and two are in database db2, all on the same MySQL server:

CREATE TABLE `db1`.`user` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL,
  `password_hash` varchar(71) DEFAULT NULL,
  `email_address` varchar(100) NOT NULL,
  `registration_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `registration_hash` char(16) DEFAULT NULL,
  `active` bit(1) NOT NULL DEFAULT b'0',
  `public` bit(1) NOT NULL DEFAULT b'0',
  `show_name` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `db2`.`ref` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `db2`.`combination` (
  `ref_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `arbitrary_number` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`figurine_id`,`user_id`),
  KEY `combination_user` (`user_id`),
  KEY `combination_number` (`user_id`,`arbitrary_number`),
  CONSTRAINT `combination_ref` FOREIGN KEY (`ref_id`) REFERENCES `ref` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `combination_user` FOREIGN KEY (`user_id`) REFERENCES `db1`.`user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table db1.user has around 600 records, the table db2.ref has around 800 records and the table db2.combination has around 300K records.

Now using Perl and DBD::mysql I perform the following query:

UPDATE `db1`.`user` SET `id` = (`id` + 1000)
ORDER BY `id` DESC

However, this query always stops mentioning the connection to the MySQL server was lost. Also executing this same query via PhpMyAdmin results in a timeout. Somehow the query just takes a very long time to execute. I guess it comes because all the foreign key values need to be updated.

Setting FOREIGN_KEY_CHECKS variable to OFF will not update the user_id column in the db.combination table, which do need to be updated.

I have also tried to manipulate the different timeouts (as suggested all over the internet), like this:

SET SESSION net_read_timeout=3000;
SET SESSION net_write_timeout=3000;
SET SESSION wait_timeout=6000;

I have verified that the new values are actually set, by retrieving the values again. However, even with these long timeouts, the query still fails to execute and after about 30 seconds the connection to the MySQL server is again lost (while amidst executing the UPDATE query)

Any suggestions on how to speed up this query are more than welcome.

BTW: The PK columns have a very large integer type. I will also make this type smaller (change to INT). Could this type change also improve the speed significantly?

UPDATE I also performed an EXPLAIN for the query and it mentions in the Extra column that the query is doing a filesort. I would have expected that due to the indexes on the table (added them, as they were not there in the first place), no filesort would take place.

Why would you be adding anything to an Auto Increment column?? Once a row in that table is created, leave the id column well alone — RiggsFolly
– RiggsFolly, Commented Jul 19, 2021 at 12:08
In bigint(20), the number 20 represents how the data will be displayed. It does not affect the storage. The number is used to display width. BIGINT takes 8 bytes i.e. 64 bits. The signed range is -9223372036854775808 to 9223372036854775807 and unsigned range takes positive value. The range of unsigned is 0 to 18446744073709551615. — RiggsFolly
– RiggsFolly, Commented Jul 19, 2021 at 12:17
I need to add a number to the column to prepare for merging with another database, that has the same structure. The value that I want to add makes room for the data from the other database's tables. — Marcel V.
– Marcel V., Commented Jul 19, 2021 at 17:19
@RiggsFolly I know that the number just represents how the data will be displayed and does not affect the storage. That is why in a later stage I want to change the storage type to a smaller type (like INT). — Marcel V.
– Marcel V., Commented Jul 19, 2021 at 17:20

Rick James · Accepted Answer · 2021-07-21 15:47:54Z

0

The 300K CASCADEs is probably the really slow part of the task. So, let's avoid it. (However, there may be a check the verify the resulting links; this should be not-too-slow.)

Disable FOREIGN KEY processing
Create new tables without FOREIGN KEYs. new_user, new_combination. (I don't know if new_ref is needed.)

Do this to populate the tables:

 INSERT INTO new_xx (user_id, ...)
     SELECT user_id + 1000, ...;

ALTER TABLE new_xx ADD FOREIGN KEY ...; (for each xx)
`RENAME TABLE xx TO old_xx, new_xx TO xx;
`DROP TABLE old_xx;
Enable FOREIGN KEY processing

answered Jul 21, 2021 at 15:47

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update index values extremely slow on MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related