SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Binary tree store in php mysql?

    I am working on a MLM project in which i have more than 30,000 members followed by the root member,i have to store member automatically in binary tree form like

    1 is root
    2 is child of 1
    3 is child of 1

    Then 4 is child of 2
    5 is child of 2

    and so on ......


    I have calculate each members left count and right count in downline, calculation takes 30minutes for 10,000 members, now i have more than 30,000 members it takes more time.... How to avoid this.....

    Here is my table structure

    ID Root MemberID lft rgt
    1 0 1000 8 8
    2 1000 1001 0 7
    3 1001 1002 1 6
    4 1002 1003 2 5


    can any body help me ...

  2. #2
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    30min?

    #1 explain your query, and add the necessary indexes.

    #2 if you do it in php, it will take under a few sec. 30k records is not much.
    Say you select 3 INT fields, that's 4 Bytes each, for 30k records, that's ~360,000 Bytes (2.74MB) of transfer from your sql server to your web server.
    Once you have the data, you make yourself a nice recursive function, that will take all that data, and find out how many people you have on your left/right.
    Then, you update the database (update 2 INT fields where 1 INT).

    There's no way that should take you more than a minute or two.

  3. #3
    SitePoint Evangelist AlienDev's Avatar
    Join Date
    Feb 2007
    Location
    UK
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Vali View Post
    30min?
    [snip]
    There's no way that should take you more than a minute or two.
    Completely agree. It sounds like OP isn't using any indexes. That is a small data set and a simple calculation.
    Me on StackOverflow | Blog & personal website.

    I mostly use: PHP, Java, JavaScript, Android.

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Pretty sure there's an algorithm you can apply. For it to be taking that long, I would suspect your making multiple calls to the database to resolve relationships.

    This maybe applicable. Most interesting.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •