MySQL stored procedure vs. multiple selects

Question

Hi all,

Here's my scenario:

I've got a table of (let's call them) nodes. Primary key on each one is simply "node_id".

I've got a table maintaining a hierarchy of nodes, with only two columns: parent_node_id and child_node_id.

The hierarchy is maintained in a separate table because nodes can have an N:N relationship. That is to say, one node can have multiple children, and multiple parents.

If I start with a node and want to get all of its ancestors (i.e. everything higher up the hierarchy), I could either do several selects, or do it all in one stored procedure.

Anyone with any practical experience with this question know which one is likely to have the best performance? I've read things online that recommend both ways.

Answer 1

If performance is your concern, then that schema design is not going to work as well for you as others could.

See More Trees & Hierarchies in SQL for more info.

Answer 2

I think a general statements could lead into problem, because it depends on how you your queries respectively the stored procedure make of usage of the indices. To make a helpful declaration it would be necessary to compare the SQL of your selects and the stored procedure.

Answer 3

"which one is likely to have the best performance? " : No one can know ! The only thing you can do is try both and MEASURE. That's sadly enough the main answer to all performance related questions... except in cases where you clearly have a O(n) difference between algorithms.

And, by the way, "multiple parents" does not make a hierarchy (otherwise I would recommend to read some books by Joe Celko) but a DAG (Direct Acyclic Graph) a much harder beast to tame...