top friends setup

snapbackz

Hi,

I have a table in my database which connects 'friends' on my website. 2 rows for each relationship that connects the two friends. I was wondering what the best structure would be for a 'top friends lists' sorta like myspace.

My first thought is to just add a 'order' column to the friend table but if a user has say 100,000 friends, it would have to update the order of all 100,000 rows. This doesn't seem very good.

Another idea would be to create a new table 'top friends' and enter the friends a user picks into that.

Anyone have experience with this? or just offer advice on what sounds like the most efficient way?

Thank you!

bretticus

You could use an ORDER BY clause with a LIMIT

snapbackz

Thanks for your input. Even with the limit on a user with 600k friends it still selects all those rows. I also have the overhead of updating 600k rows each time they change the order of their top friends....

I'm leaning towards a new 'top friends' table unless I'm missing something.

bretticus

I think I may not be following you. In your first post you said that a user may have 100k friends. When you say "top friends", do you mean you want to show the top (let's say 10) friends out of that users 100k friends? This is what I understood the first time around.

So my questions are:

How do you get to be a top friend?
If a user has 600k friends, why would that involve updating all 600k records for that user?

Maybe you can show the query/queries you are using/considering.

snapbackz

How do you get to be a top friend?
You get to be a top friend when the user selects you to be. So, say I had a friends list of 100k users, I could look through that 100k list and selects X amount of users to be shown in my 'top friends' list.

If a user has 600k friends, why would that involve updating all 600k records for that user?
If a user has 600k friends, it would involve updating all 600k records to update the order column in which they are to be displayed... I thought at least. I could be wrong. Basically so you could SELECT..and ORDERY BY order

I thought an additional table might be helpful so I could put all the user selected top friends into it and only pull from that table instead of selecting all the friends plus ordering them.

bretticus

snapbackz;10879786 wrote:
How do you get to be a top friend?
You get to be a top friend when the user selects you to be. So, say I had a friends list of 100k users, I could look through that 100k list and selects X amount of users to be shown in my 'top friends' list.

Okay, I see now. Were you planning on showing all 100k friends on one page? I highly recommend you only show a manageable amount of friends per page (say like 50 perhaps.) This is done through LIMIT . Depending on how may users you have total, you can can have links to page 2 and so on. For example, Fred has 120 friends. If you show 50 friends per page, he has a link for pages 1 through 3. Also, a search function to look up friends will be very helpful. Search for "pagination" in this forum. It's been discussed numerous times.

snapbackz;10879786 wrote:
If a user has 600k friends, why would that involve updating all 600k records for that user?
If a user has 600k friends, it would involve updating all 600k records to update the order column in which they are to be displayed... I thought at least. I could be wrong. Basically so you could SELECT..and ORDERY BY order

Actually you can just update one record at a time. ie. user selects friend as top friend. As a resutl of that action, you can do an

UPDATE ... WHERE userid = $_GET['userid']

or something similar. No need to update 100k rows at once!

snapbackz;10879786 wrote:
I thought an additional table might be helpful so I could put all the user selected top friends into it and only pull from that table instead of selecting all the friends plus ordering them.

Right, you could have a two column table with owner_user_id|top_friend_user_id.