AW: Count of children - André Hänsel - com.mysql.lists.mysql

12 messages in com.mysql.lists.mysqlAW: Count of children

From	Sent On	Attachments
André Hänsel	27 Sep 2006 06:22
João Cândido de Souza Neto	27 Sep 2006 06:47
Rob Desbois	27 Sep 2006 06:48
André Hänsel	27 Sep 2006 07:00
'Rob Desbois'	27 Sep 2006 07:31
João Cândido de Souza Neto	27 Sep 2006 07:40
Peter Brawley	27 Sep 2006 07:48
André Hänsel	27 Sep 2006 07:55
Douglas Sims	27 Sep 2006 07:57
ddev...@intellicare.com	27 Sep 2006 08:03
Peter Brawley	27 Sep 2006 08:19
Robert DiFalco	27 Sep 2006 09:16

Subject:	AW: Count of children
From:	André Hänsel (haen...@creations.de)
Date:	09/27/2006 07:55:34 AM
List:	com.mysql.lists.mysql

I will use any model that is suitable. ;)

I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them.

-----Ursprüngliche Nachricht----- Von: Peter Brawley [mailto:pete...@earthlink.net] Gesendet: Mittwoch, 27. September 2006 16:49 An: André Hänsel Cc: mys...@lists.mysql.com Betreff: Re: Count of children

André,

I want the count of all sub-entries for a specific entry.

Depends on the model you are using--edge list or nested sets?

-----

André Hänsel wrote:

I have a table with id and parent_id. I want the count of all sub-entries for a specific entry.

I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem.

I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts.

Is there a smart solution/best practice for my problem?

Now I can't think of another sentence starting with an i. ;-)

Best regards, André