1, requirements,

When MySQL queries, there are many query criteria. A directory tree table, N nodes, need to loop to determine whether each node has child nodes. Because of the external network used by the database (not in the same LAN), when there are many nodes (more than 500), the efficiency is very low, because 500 sub-queries are required.

How the database is organized:

field The name of the
pg_id Id no.
pg_name The name of the node
parent_id Id of the parent node

select count(*) from os_pgroup where parent_id = 0 select count(*) from os_pgroup where parent_id = 21 select count(*) from os_pgroup where parent_id = 31 select count(*) from os_pgroup where parent_id = 40 xxxxxx

2. Solutions

  • Intranet If it is an Intranet, you can use the application program to circularly invoke MySQL, because the performance is not low due to network reasons

  • In the current extranet application scenario, you can use Group by

SELECT count(*), parent_id from os_pgroup where parent_id in (10, 21, 31, 40, xxx) GROUP BY parent_id

The results are as follows:

count(*) parent_id
0 35
21 14
31 1

In this way, the child node records can be queried out.

If the query conditions are too complex, you can use views or stored procedures (not recommended) to solve the problem