Page 1 of 1

downline efficiency

Posted: Sun Dec 16, 2007 2:04 pm
by s.dot
I am using the modified preorder tree traversal algorhythm to maintain downlines and uplines. The effiency is great, allowing me to traverse the uplines and downlines in one query wherever they're needed throughout the application.

I'm worried about once this application starts getting a decent amount of members. When new members are inserted into downlines, the left and right boundaries of a good portion of the downline table will have to be reindexed on every insert. How can I maximize efficiency of this? Is it worthy of NOT indexing those table fields and risk slower queries throughout the application?

Posted: Sun Dec 16, 2007 4:18 pm
by fredrik
It's simple, just nicrease the L/R values of the enities to the right of the inserted entity with more then 1, say you increase it with 100 then u can do 99 more insterts before you have to increase it again.

Posted: Sun Dec 16, 2007 4:30 pm
by s.dot
If the downline is dynamic (via referrals), then the downline tree is updated in different spots throughout the entire table depending on the referrer. It's not a single downline/upline.

Posted: Sun Dec 16, 2007 4:31 pm
by s.dot
Oh, nevermind, I see what you're saying. I wonder if that's a good tactic or not.

Posted: Sun Dec 16, 2007 5:16 pm
by fredrik
scottayy wrote:Oh, nevermind, I see what you're saying. I wonder if that's a good tactic or not.
Afaik it's the only simple way to optimize a MPTT-tree for inserts, other things you can do is a master/slave setup with mysql, only allowing writes to the master and reads from the slave.

Posted: Mon Dec 17, 2007 9:57 am
by Kieran Huggins
That's the main issue with MPTT, but it pays off if you have significantly more reads than writes.

Keep in mind it will affect the entire tree to the right of the affected node. If your tree grows large enough (thousands+) you could have performance issues at modification time.