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

MySQL SQL

プロジェクトID: #597474

プロジェクトについて