Data structure - PHP's problem with mysql database traversal

An algorithm optimization problem about the agent distribution system

For example, the agent levels are divided into three levels: gold, silver and bronze. I am now gold agent A. At the same time, I have developed silver agents B, C and D. Silver agent b has developed bronze agents E and F, as shown in the picture:
A List of subordinate agents
╦═══════
║
╠═ b
║ ╠══ e
║ ╠══ f
╠═ c
╠═ d
I now use the program Make an example like the one above The diagram method is: (PHP+MYSQL)
First search for all agents whose superior agent is A.
For example, if agent B is found, then search for all agents whose superior agent is B. This search is completed.
Search for agent C again…………
and so on.

Question:

There are now 300,000 records in the agent database. Each agent can view his own subordinate agent tree in the agent distribution system. Follow the above method:
Every search will take a long time. If an agent has 1,000 subordinate agents , then it won’t be displayed at all.

The solution I thought of is to use an array to store all user relationships, and then store this array as a file. Each time a user is added or deleted, the array will be updated at the same time, and then the desired data will be traversed from the array, and then Just go directly to the database and execute a select. . Is this method feasible? Are there any other solutions?

A List of subordinate agents
╦═══════
║
╠═ b
║ ╠══ e
║ ╠══ f
╠═ c
╠═ d
I now use the program Make an example like the one above The diagram method is: (PHP+MYSQL)
First search for all agents whose superior agent is A.
For example, if agent B is found, then search for all agents whose superior agent is B. This search is completed.
Search for agent C again…………
and so on.

Every search will take a long time. If an agent has 1,000 subordinate agents , then it won’t be displayed at all.

If you want to find the bottom members from the upper level members, you will use traversal. Visually, it is the level traversal of the ternary tree. This algorithm will visually query the database many times. . . It consumes too much resources. Are there any alternatives? cache? redis?

Such an implementation can look at infinite levels of classification, use the left and right value principle, and traverse the tree structure in order, which is the same as the classification of shopping malls. Principle

It’s not appropriate to display the entire tree on one page. It can be queried on demand.

If the updates are not very frequent, use
1. cache

   (the data volume is 300,000, it is estimated that it can only cache level 1~2), instead of using SQL query every time.

Load multiple times, as mentioned above, first load the
2. N level

   , wait for the click, and then use ajax to request the N+1 level.

Search for the specific answer yourself. It’s very troublesome to explain this in detail. I’ll give you the general principle.
How can you know who your subordinates are as quickly as possible? If everyone comes to stand in line, just meet two conditions, 1- you know who is the first, 2- ensure that you are the last (of course you can also know who is the last, and ensure that you are the first)
According to this It can be inferred that fast search can be achieved by assigning an appropriate serial number to each node, such as select * from tree where indexNumber >= search.node.min && indexNumber < search.node.indexNumber

The final table structure is similar
id, parent_id (parent node), top_id (root node, if there are multiple trees), indexNumber (index number in the tree, top_id+indexNumber is unique), min (I am Benchmark, who is the first under this branch), level (tree height)

For your example it should be similar (the first number in brackets is the index number, the second is min, the third is the tree height)

<code>            a(6,1,0)
     b(3,1,1)      c(4,4,1)      d(5,5,1)
e(1,1,2) f(2,2,2)</code>

This structure is more complicated when operating nodes (for example, if you add a g after f, or delete f, then abcd needs to recalculate the sequence number), but the search is very fast. Generally, the results can be obtained in one search.