This content originally appeared on Level Up Coding - Medium and was authored by Stephan Romhart
Performant Trees for Websites with MySQL — Nested Sets and Alias Paths
A simple tutorial that shows how to map performant tree structures in MySQL using the nested sets model.
I use the nested set model for websites, not for forum or comment software. There are enough articles showing how complex nested sets are in a forum environment. Since websites usually have more read than write access to the database for their structure, this is not an issue here.
Starting point
To explain the principle, I take a structure of a very small website: “Project” is the root, “Start”, “Company”, “Contact” are the branches in depth 1. “Company” still has the sub-branches “Services” and “Management”.
The Concept
In nested sets, each branch of a tree has two values: “left” and “right”. The root always has 1 as the left value.
For example, the left value can be used to read the sort order.
1 Project
2 Start
4 Compamy
5 Services
7 Management
10 Contact
The number of sub-branches of a branch can be calculated using the following formula (“right” — “left” — 1) / 2. Using the “Company” branch as an example:
(9 - 4 - 1) = 2
More examples for creating, editing and deleting branches can be found in abundance on the net. I have attached a few links to the article below.
Read talking URLS performant with MySQL
I have built the above example in MySQL. The table “pages” has the following columns: “id”, “title”, “alias”, “lft”, “rgt”.
The field “alias” can now be used to read out the path of the respective page via a sub select:
Read branch depths with sub select
With another sub select, the depth of the branches can also be read out as follows:
The complete MySQL query with alias paths and branch depths.
If you now combine both, you get all the values you need to build a website structure.
Bonus: The table can easily be extended with a column “language” to achieve a multilingual website. For each language a separate root page is needed.
Convenient: With a short supplementary where clause at the end of the query, the root page can be removed from the result:
...
FROM pages
WHERE pages.lft!=1
...
I have done very well with this method over the last few years and have implemented various projects with multiple languages and > 50 subpages. The performance was very good even on small webspaces.
If there are better and more performant ways to display website trees with MySQL, please let me know! I am looking forward to your feedback on this topic!
Nested Sets and Alias Paths — Performant Trees for Websites with MySQL was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Stephan Romhart
Stephan Romhart | Sciencx (2021-11-19T17:09:03+00:00) Nested Sets and Alias Paths — Performant Trees for Websites with MySQL. Retrieved from https://www.scien.cx/2021/11/19/nested-sets-and-alias-paths%e2%80%8a-%e2%80%8aperformant-trees-for-websites-with-mysql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.