My advice is to consider an alternate storage approach. Instead of storing parent id's, store the trees using the Nested Set model. http://www.dbmsmag.com/9603d06.html
About halfway down the article he presents a model where you never store the parent id, but you do store two columns (LEFT and RIGHT) which represent the boundaries of the child records, and their respective LEFT and RIGHT information. That sounds complicated, and it is. But it is an ANSI-compliant solution, and it can be used to get terrific results. His article is followed up in the april and may issues, as well, also online. In addition, you can follow up his article by looking in Usenet groups for the term "Nested Set Hierarchy" or "Nested Set Model". Good luck with it. Kevin -----Original Message----- From: Grégoire Dubois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 6:54 AM To: [EMAIL PROTECTED] Subject: Conception - Tree - Recursivity -Address book - Query speed Hello all, I am putting multiple "address book" (trees) into a MySQL database. These "address book" are made of "directories" and "persons". It gives something like that for the tables : Directory -------------- ID Name ID_father (the reference to the father directory) Person ------------- ID Name ID_father (the reference to the father directory) Creating the tree doesn't give me problem. Where I ask me some questions, is the speed to get the whole tree from the database in a recursive way. The way the tree is defined in the database, I will be obliged to make for each node a SELECT to know all its children, then do the same for the children, etc... Each SELECT will give me a ResultSet. And from each ResultSet I will know if the node has got childrens, and if yes, I'll have to make new requests to the database (one for each child), etc... As I will have a lot of clients that will ask for those trees, I am wondering if this technique won't take too much ressources and be too slow. I know that Oracle uses CONNECT BY PRIOR for this type of problem. But, as this functionnality isn't available yet in MySQL, I'm asking you for the best solution you would think of. Thank you very much for your help. PS : Any website talking about database conception would be very helpfull to me if you know some. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
