Data structure-mysql database traversal in php

An algorithm optimization problem about the agent distribution system, for example, the agent level is gold, silver, and copper. I am currently A gold agent, at the same time, I developed silver agents B, C, and D. Silver agents B developed bronze agents E and F, the list of lower-level proxies of A is too large... an algorithm optimization problem about the agent distribution system

For example, the agent level is divided into three levels: gold, silver, and copper. I am now A gold agent, and I have developed silver agents B, C, and D, silver Agent B has developed bronze agents E and F,
List of lower-level proxies of
When there are already too many other users
Bytes
╠ ═ B
║ ╠ ═ E
║ ╠ ═ F
╠ ═ C
Mongomongod
I am using a program to make the above example: (PHP + MYSQL)
First, search for all agents whose superiors are,
For example, if proxy B is found, and then all proxies with proxy B are searched, this search is complete.
Search for the C proxy ............
And so on.

Problem:

Currently, the proxy database has 0.3 million records. each proxy can view its own lower-level proxy tree in the proxy distribution system. follow these steps:
It takes a long time for each search. If a proxy has 1000 lower-level proxies, it cannot be displayed at all.

The solution I have come up with is to use an array to store all user relationships, store the array as a file, and update the array each time a user is added or deleted, then, traverse the desired data from the array and execute a select statement directly in the database .. Is this method feasible? is there any other solution?

Traversal

If you want to find the bottom-layer members from the upper-layer members, traversal will be used. the visual view is the layered traversal of the three trees. this algorithm will perform many queries on the database... Resource-consuming. Is there any alternative? Cache? Redis?

Reply: an algorithm optimization question about the agent distribution system

For example, the agent level is divided into three levels: gold, silver, and copper. I am now A gold agent, and I have developed silver agents B, C, and D, silver Agent B has developed bronze agents E and F,
List of lower-level proxies of
When there are already too many other users
Bytes
╠ ═ B
║ ╠ ═ E
║ ╠ ═ F
╠ ═ C
Mongomongod
I am using a program to make the above example: (PHP + MYSQL)
First, search for all agents whose superiors are,
For example, if proxy B is found, and then all proxies with proxy B are searched, this search is complete.
Search for the C proxy ............
And so on.

Problem:

Currently, the proxy database has 0.3 million records. each proxy can view its own lower-level proxy tree in the proxy distribution system. follow these steps:
It takes a long time for each search. If a proxy has 1000 lower-level proxies, it cannot be displayed at all.

The solution I have come up with is to use an array to store all user relationships, store the array as a file, and update the array each time a user is added or deleted, then, traverse the desired data from the array and execute a select statement directly in the database .. Is this method feasible? is there any other solution?

Traversal

If you want to find the bottom-layer members from the upper-layer members, traversal will be used. the visual view is the layered traversal of the three trees. this algorithm will perform many queries on the database... Resource-consuming. Is there any alternative? Cache? Redis?

It is recommended that you perform hierarchical queries and query data as needed. a one-time link tree is displayed with a large number of queries, consuming resources;
In this way, we can look at the infinite classification. We use the left-right value principle to traverse the tree structure in sequence, which is the same as the classification of malls.

Check whether the agent-level index is created.

It is not suitable for displaying a page in the entire tree. it can be used as an on-demand query.
The "gold agent" page displays all the lower-level Silver agents. click the silver agent to view the lower-level bronze agent.

Thank you for inviting me to talk about some of my ideas:

1. If the update is not frequent, useCache(The data size is 0.3 million. it is estimated that only 1 ~ can be cached ~ Level 2), you do not need to use SQL queries each time.

2. Load multiple times. This upstairs also says, first loadLevel NWait until you click, and then send a request via ajax.N + Level 1.

Tree structure Infinitus classification

Search for the specific answer by yourself. it is very troublesome to explain it in detail. let's explain the general principle to you.
How can we quickly know who is my subordinate? If you come to the station, you can meet two conditions: 1-you know who is the first, 2-ensure that you are the last one (you can also know who is the last one, make sure you are the first)
According to this inference, each node can be quickly searched by adding a proper sequence number, for example, 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 the benchmark, who is the first in this branch), level (tree height)

Your example should be similar (the first digit in the brackets is the index number, the second is min, and the third is the tree height)

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

This structure is complex in node operations (for example, if you add g after f or delete f, then abcd needs to re-calculate the serial number), but the search is very fast, generally, a search result is returned.