Phil Waclawski: Using hierarchical data in mysql trees vs nests

Presented to the Phoenix Linux Users Group (PLUG) on April 13th 2017 by Phil Waclawski.
Real world data is often made of hierarchical relationships, or "trees".
Representing that data in a Relational Database starts to get complicated as soon as you have more than two levels in your "Tree".
There are many strategies for handling such data, and I will cover examples of at least two of them (adjacency list and nested sets) and possibly more.
If you have access to a mysql instance, you can create the test tables and follow along, or get the code later.
About Phil:
Phil has been a faculty member, focusing on open source software at MCC for nearly two decades.
Phil teaches a variety of course, including MySQL Database, PHP, Python, Javascript, HTML 5 and CSS, Linux OS, Internet Server Set up and more.
This summer, Phil will once again be offering a "MCC Multimedia Madness" summer camp for 6-8th graders. Over the two weeks I will cover a wide range of open source software, including GIMP, Inkscape, Krita, Audacity, Blender, Hydrogen Drum Kit, Tux Guitar, Openshot, Linux Multimedia Studio, work with green screen and more.
For hobbies, Phil has too many. Blacksmithing, archery, Leatherworking, Wood Working, Jewelry, Sound Recording/editing and SCUBA diving. Phil is also learning to play the Bass.
Phil has been using linux at home for desktops and servers since 1994.
If you like what you see here, the Phoenix Linux Users Group meets several times a month. Please visit PhxLinux.org for meeting times and locations then come see the presentations live and uncut.

[@rafagd]

You can also mitigate the nested set impact if you partition it on something.
For engine parts you could have a new forest for every engine_id, for example. This way one engine doesn't affect the other.
The one time I've implemented it in the wild, we had a combination of both parent_id and left/right, this way I could both reindex if something went badly, and I could search for direct children more easily if I wanted to.
Apparently it's possible to have similar perf to nested sets nowadays with recursive queries, but it is a tool I'll keep in my box forever even if I never use it again.

[@g9223701]

Thanks for this tutorial. It is very easy to understand and helpful.

[@BelalHossain-zr9yg]

Thanks :)

[@shreysom2060]

For making a folder and file type system where a folder is parent and have many children including folder and file. I am storing all the meta data in the sql Table specifying the parent id and base parent id. Which technique will be best fit for retrieving the child of a particular folder provide that it should be Efficient in term of moving a folder and deleting as well.

[@birajdardnyaneshwar]

What did you use?

[@binarylife1100]

When i type the query to view entire tree for NESTED SETS ( level 41:06 of video), i get error below
#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'treepractice2.node.lvalue' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[@nameethukku]

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION';

[@salmansaysit]

Where can I have these slides anyone ?

[@BrianCluff]

They can be downloaded from phxlinux.org/presentations/mysql_trees.zip, just open the index.html file, after uncompressing, to view them.

[@salmansaysit]

Thanks I have been trying to get my head around this mystery for about 10 year and no one is been able to explain the way this video .
Very precious piece of information

[@PabloPazosGutierrez]

Right click copy, right click paste OMG