Hierarchical Data & Performance: MySQL Stored Procedures

* Background *

We store hierarchical (tree) data in a MySQL database (cf. attached pdf file). Because tree nodes can have multiple children and multiple parents, we added a table which stores the parent-child relationships.

To display a tree from a given node A, the children of node A are first fetched and analysed. Then, the children of the fetched children are fetched, and so on... This recursive procedure is quite slow when the trees grow bit (many database queries).

* Project Goals *

We want to overcome these performance issues by adding stored procedures to the database. It must be possible to answer these questions as efficiently as possible:

- What is the maximum tree depth from a given node: get_tree_depth(from_node, include_deleted) (NB: an object can be marked deleted and must be excluded from the analysis depending on the flag include_deleted)

- Get the number of children from a given node: get_num_children(from_node, include_deleted)

- Get children from a given node: get_children(from_node, include_deleted) (this should return a table with the following columns: object_id, child_id, title. If a node has multiple children, it will have multiple rows in this table)

- Get full path of a given node: get_path(from_node) (opposite from get_children)

This work can be based on [url removed, login to view] and [url removed, login to view]

As in the sql script referenced, it should be possible to combine the calls with select queries:

CALL get_children(1, True, @a);

SELECT @a as 'children_below_1';

Skills: MySQL, SQL

See more: mysql select hierarchical data, mysql hierarchical tree stored procedure, mysql hierarchical data, store child nodes mysql, mysql select procedure performance, mysql hierarchical data store tree path, mysql hierarchical procedure tree, hierarchical object store performance, mysql children stored procedure, mysql full tree procedure, hierarchical data sql stored procedure, mysql parent child depth, hierarchical mysql, mysql hierarchical tree, mysql stored procedure hierarchical data, mysql hierarchical stored procedure, efficiently store hierarchical data, mysql multiple child parents, what is recursive, what is a tree node, tree node, table trees, sql pdf file, return path, opposite of maximum

About the Employer:
( 40 reviews ) Baar, Switzerland

Project ID: #597474

Awarded to:


Sir, please check the PMB!thanks!

$100 USD in 1 day
(2 Reviews)

4 freelancers are bidding on average $138 for this job


Hello, I think I can help you with this task. I have experience with MySQL and Oracle - queries, stored procedures. I have also used MySQL to extract similar hierarchical data in an application, targeted to a privat More

$50 USD in 1 day
(3 Reviews)

I am interested in your job for the following reasons: (1) Senior Developer and Architect (2) Enterprise applications (3) Master Degree in Computer Science (4) Up-to-date (5) TA in Systems and Computer Engineerin More

$200 USD in 7 days
(0 Reviews)

I can do the job.

$200 USD in 6 days
(0 Reviews)