Em Andamento

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

Habilidades: MySQL, SQL

Ver mais: mysql select hierarchical data, mysql hierarchical data, store child nodes mysql, mysql select procedure performance, mysql hierarchical procedure tree, hierarchical object store performance, mysql children stored procedure, mysql full tree procedure, mysql parent child depth, hierarchical mysql, mysql hierarchical tree, mysql hierarchical stored procedure, efficiently store hierarchical data, mysql multiple child parents, tree node, table trees, return path, opposite maximum, number nodes tree, trees nodes, node tree, mysql questions, maximum path, depth first, data trees

Acerca do Empregador:
( 40 comentários ) Baar, Switzerland

ID do Projeto: #597474

Premiar a:

alanding

Sir, please check the PMB!thanks!

$100 USD em 1 dia
(2 Avaliações)
2.0

4 freelancers estão ofertando em média $138 para este trabalho

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 Mais

$50 USD em 1 dia
(3 Comentários)
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 Mais

$200 USD in 7 dias
(0 Comentários)
0.0
tahaBestShore

I can do the job.

$200 USD in 6 dias
(0 Comentários)
0.0