0

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';
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Banana Tech
  • 347
  • 1
  • 6
  • 18
  • Updating a huge table in one UPDATE is not efficient . Try updating in a loop. In every step update for example 10K or 100K according your workload. – Meyssam Toluie Mar 07 '22 at 10:27
  • What [estimated] percantage of the rows matches the predicate `RGT > 2`? – The Impaler Mar 07 '22 at 13:57
  • The collation you use makes me think you are using MySQL 8.0, which now supports [recursive queries](https://dev.mysql.com/doc/refman/8.0/en/with.html). There's no need to use the denormalized nested-sets design for hierarchical data in MySQL. Have you considered just using a plain `parent_id` design for your tree, which would eliminate the need for expensive queries to update the tree? – Bill Karwin Mar 07 '22 at 16:58
  • @TheImpaler low .. let's take average 50% predicate RGT > 7500 where total row of data 15k – Banana Tech Mar 08 '22 at 04:41
  • @BillKarwin parent_id is define in another table.. probably u may need to check Mike Hillyer design about lft/rgt table design http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ – Banana Tech Mar 08 '22 at 04:44
  • Yes, I'm familiar with the nested sets design (Mike Hillyer didn't invent it). You might like my presentation [Models for Hierarchical Data](https://www.slideshare.net/billkarwin/models-for-hierarchical-data) or my answer on SO to the question [What is the most efficient/elegant way to parse a flat table into a tree?](https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462) – Bill Karwin Mar 08 '22 at 14:44
  • Bluntly put, a left-right tree is grossly inefficient. Don't use it for any large dataset. – Rick James Mar 08 '22 at 15:42
  • 1
    Updating half of 15K rows requires looking at all 15K, saving (for crash) the current values for the half you will change, changing that half, then tossing the saved values. A lot of work! – Rick James Mar 08 '22 at 15:44

0 Answers0