How to Handle 'Friends' in a MySQL Database?

I am wondering how a typical network with your generic 'Friends' system would handle using this friends system with a MySQL database.

I assume one would store friends in the database in a single VARCHAR column, as the maximum amount of storable bytes are 65,535, so doing the math that allows about 1,600 friends minus a few I believe, supposing you store the UUID of the friends, or about 6,500 if you assign a custom ID value to a player hoping the amount of players on your server doesn't exceed 1 billion ( lol ).

I did some more math, and if you get a million users that only takes about 65gb if I did the math correctly.

Therefore, that way seems ideal in terms of storage. But a concern I have is retrieving this data if a player has a LOT of friends. Some questions I have regarding this are:

- Would I be able to cache this much data per user? Assuming the network gets about 10,000 players on (assuming that many to be safe), that would be about 6.5gb if every player has the maximum amount of friends, which I feel like is a lot of data to cache. But then again I assume Hypixel caches their friends as their /friend list command is instantaneous. So I'm not sure what to do here.

- Would retrieving an extensively long string from MySQL and then taking that string and splitting it and storing all of the values in a Java list be too resource intensive to do? If not, would it be quick enough to do when the player is logging into the server?

- Am I just completely wrong with how I'm assuming servers like Hypixel manage their friends? If so whats the ideal way to store and load friends and have pretty much instantaneous commands such as listing out friends and those who are online and not?

I'd love any help anyone provides, as I've been thinking on this for several days and everything I think of I feel like is flawed in some way. Thanks!!

 

Did you tried doing /friends on hypixel with 6500 friends?

 

@CrystalxNeth

Those are some relatively pointless numbers you've got there. First off, a VARCHAR field only takes up as much space as it needs (it's length is variable, hence the name "VARCHAR"). Second, you should never, ever, ever, be storing literal lists of information in SQL. It completely defeats the purpose of a relational database, at that point you may as well just be using YAML files, honestly.

Instead, anywhere there is duplicity in a field, you make another table. For example, instead of having one entry with all the friends in a list, you would have many entries, each with two values (one friend's id and the other friend's id). This is called "Normalisation."

It should be an imperceptible delay for the user when you select from something like this, assuming they ask for it via command (80ms is a lot for a tick, but a very small delay for user input).

 

One table with the players, and one table with the connections using the ids from the first tavle would do it.

 

So basically I have 2 columns; user ID #1 and user ID #2, and that represents a friendship. Then I can run two queries such as: "SELECT userID1 ... WHERE userID2 = 'the user's ID'" and "SELECT userID2 WHERE userID1 = 'the user's ID'" and get the friends from those result sets?

Also would it be reasonable to then run a query to get a list of the user's friends' names from a player data table such as "SELECT playerName ... WHERE userID='friend1ID' OR userID='friend2ID OR etc.." or should I retrieve the UUID from the player data table and retrieve the users current username from the internet? I feel like the second option would surely take a long amount of time, but surely be more reliable.

Also should I cache this data or just retrieve it anytime I need it? I will most likely be using it a lot so I'm worrying about whether or not the database or servers running the queries will have their performance impacted by this or not.

 

@CrystalxNeth I don't work with servers or databases but I do have a bit of input here that I would mark as logical reasoning. Most people don't play with all 6500 of their internet friends at once. (For me, 5 is a bit much.) Wouldn't it make sense to only store the friends that they most commonly play with locally so that you can quickly access all their favorite users for playing together and then just query the database if that weekend comes along where Swagzilla862 finally decides to play with UltimateWeaboo46 (completely arbitrary usernames)?

 

@CrystalxNeth

Use a cache only if it becomes an issue, or you commonly need it from the main thread of the server. You should only use one query, and the table should be indexed by both. Use the integer value of the UUID in the SQL server to make it even faster (if you want). The query should be "SELECT * FROM friendship WHERE uid1 = ? or uid2 = ?" and just make those the same value. That will give you what you're looking for.

 

Okay, lastly; What do you mean by this^?