Re: materialized path performance problem
Posted by: Bill Karwin
Date: July 11, 2006 10:46AM

I don't recommend the materialized path solution. You should probably use a normalized schema to store paths, not a materialized path string.

For example, I've used a schema like this with success:

CREATE TABLE `instr_kategorie` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;

CREATE TABLE `instr_kategorie_path_enum` (
`parent_id` mediumint(8) unsigned NOT NULL,
`child_id` mediumint(8) unsigned NOT NULL,
`path_length` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`parent_id`, `child_id`),
FOREIGN KEY (`parent_id`) REFERENCES `instr_kategorie`(`id`),
FOREIGN KEY (`child_id`) REFERENCES `instr_kategorie`(`id`)
) ENGINE=InnoDB;

Then if kategorie 1 has children 2 and 3, and kategorie 3 has children 4 and 5, you would store:
(1,2,1), (1,3,1), (3,4,1), (3,5,1), (1,4,2), (1,5,2)

Then add child 6 to parent 5:
(5,6,1), (3,6,2), (1,6,3)

Then you can get all indirect children of kategorie 1:

SELECT c.*
FROM `instr_kategorie` k
JOIN `instr_kategorie_path_enum` p ON k.id = p.parent_id
JOIN `instr_kategorie` c ON p.child_id = c.id
WHERE k.id = 1

A different implementation of heirarchical structures in SQL is described here: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Options: ReplyQuote


Subject
Written By
Posted
Re: materialized path performance problem
July 11, 2006 10:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.