downline efficiency

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

downline efficiency

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
fredrik
Forum Newbie
Posts: 13
Joined: Sun Nov 04, 2007 4:41 am

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Oh, nevermind, I see what you're saying. I wonder if that's a good tactic or not.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
fredrik
Forum Newbie
Posts: 13
Joined: Sun Nov 04, 2007 4:41 am

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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.
Post Reply