Binary tree store in php mysql?

PHP
1

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

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

Completely agree. It sounds like OP isn’t using any indexes. That is a small data set and a simple calculation.

4

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. :slight_smile: