How to optimize update statement

67 Views Asked by At

I am maintaining the Mike Hillyer Hierarchical Data in MySQL.

Q) How to optimize the update statement below. It will take average about 500 Milliseconds

update AGENCY_TREE set RGT = RGT - 2  where RGT > 2;

The table consist around 15k data. Structure as below

CREATE TABLE IF NOT EXISTS `user_tree` (
  `USER_ID` bigint NOT NULL COMMENT 'The user ID',
  `LFT` bigint NOT NULL COMMENT 'Left boundary of all children',
  `RGT` bigint NOT NULL COMMENT 'Right boundary of all children',
  PRIMARY KEY (`USER_ID`),
  UNIQUE KEY `U_USER_TREE_LFT` (`LFT`),
  UNIQUE KEY `U_USER_TREE_RGT` (`RGT`),
  CONSTRAINT `FK_USER_TREE_AGENCY` FOREIGN KEY (`USER_ID`) REFERENCES `agency` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='USER hierachy tree';
0

There are 0 best solutions below