Hierarchical Data & Performance: MySQL Stored Procedures

Completed Posted Jan 25, 2010 Paid on delivery
Completed Paid on delivery

* 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';

MySQL SQL

Project ID: #597474

About the project

4 proposals Remote project Active Jan 30, 2010

Awarded to:

alanding

Sir, please check the PMB!thanks!

$100 USD in 1 day
(2 Reviews)
2.0

4 freelancers are bidding on average $138 for this job

DanielNikolov

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)
2.9
halmarakeby

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)
0.0
tahaBestShore

I can do the job.

$200 USD in 6 days
(0 Reviews)
0.0