在mysql中处理大型递归查询的最佳方法是什么？

Question

Using PHP & Mysql-

I have a list of 120,000 employees. Each has a supervisor field with the supervisor employee number.

I am looking to build something that shows the employees in a tree like format. Given that if you click on anyone that you have an option to download all of the employees (with their info) that are under them.

So two questions - should I write my script to handle the query (which I have but is SLOW) or should create some sort of helper table/view? I am looking for best practice behind this.

Also I am sure this has been done a million times. Is there a good class that handles organization hierarchy?

Answer 1

The standard way of doing this is to use one table to store all of the employees, with a primary key field for the employee_id, and a field for supervisor_id which is a 'self join' - meaning that the value in this field points back to the employee id of this employee's supervisor. As far as displaying the employee tree - for relatively small trees, the entire tree structure can be sent to the client's browser when the page is created, and tree nodes can be displayed as the nodes are clicked from the stored data. But, for larger trees, it is better to fetch the data as needed, i.e. when the nodes are clicked. If you have 120,000 employees, then you might want to use the later approach.