ramachandran — 2010-03-12T05:17:14-05:00 — #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 ...
vali — 2010-03-12T13:39:08-05:00 — #2
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.
aliendev — 2010-03-12T13:48:59-05:00 — #3
Completely agree. It sounds like OP isn't using any indexes. That is a small data set and a simple calculation.
anthonysterling — 2010-03-12T13:53:40-05:00 — #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.