Databases
StackOverflow2013
Postsdbo
POBest way to scale data, decrease loading time, make my webhost happy
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest way to scale data, decrease loading time, make my webhost happy
    primarykey
    data
    text
    <p>For a Facebook Application, I have to store a list of friends of a user in my MySQL database. This list is requested from my db, compared with other data, etc.</p> <p>Currently, I store this list of friends within my user table, the uids of the friends are put together in one 'text' field, with a '|' as separator. For example:</p> <blockquote> <p>ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321|123456|765432</p> </blockquote> <p>My PHP file requests this row and extracts the list of friends by exploding that field ('|'). This all works fine, every 1000 users are about 5MB diskspace.</p> <p><strong>Now the problem:</strong></p> <p>For an extra feature, I also need to save the names of the friends of the user. I can do this in different ways:</p> <p>1) Save this data in an extra table. For example:</p> <blockquote> <p>ID - UID - NAME => 1 - 1234321 - Jane Doe</p> </blockquote> <p>If I need the name of the friend with ID 1234321, I can request the name from this table. However, the problem is that this table will keep growing, until all users on Facebook are indexed (>500million rows). My webhost is not going to like this! Such a table will take about 25GB of diskspace.</p> <p>2) Another solution is to extend the data saved in the user table, by adding the name to the UID in the friends field (with an extra separator, let's use ','). For example:</p> <blockquote> <p>ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321,Mike Jones|123456,Tom Bright|765432,Rick Smith</p> </blockquote> <p>For this solution I have to alter the script, to add another extra explode (','), etc. I'm not sure how many extra diskspace this is going to take... But the data doesn't get easy to handle this way!</p> <p>3) A third solution gives a good overview of all the data, but will cause the database to be huge. In this solution we create a table of friends, with a row for every friendship. For example:</p> <blockquote> <p>ID - UID - FRIENDUID => 1 - 123456789 - 54321</p> <p>ID - UID - FRIENDUID => 3 - 123456789 - 65432</p> <p>ID - UID - FRIENDUID => 2 - 987654321 - 54321</p> <p>ID - UID - FRIENDUID => 4 - 987654321 - 65432</p> </blockquote> <p>As you can see in this example, it gives a very good overview of all the friendships. However, with about 500million users, and let's say an average of 300 friendships per user, this will create a table with 150billion rows. My host is definitely not going to like that... AND I think this kind of table will take a lot of diskspace...</p> <p>So... How to solve this problem? What do you think, what is the best way to store the UIDs + names of friends of a user on Facebook? How to scale this kind of data? Or do you have another (better) solution than the three possibilities mentioned above?</p> <p>Hope you can help me!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRobin
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBest way to scale data, decrease loading time, make my webhost happy
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POBest way to scale data, decrease loading time, make my webhost happy
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POBest way to scale data, decrease loading time, make my webhost happy
      1. This table or related slice is empty.
      1. VTUpMod
    1. COfind a tutorial on Database normalization
      singulars
      1. POBest way to scale data, decrease loading time, make my webhost happy
      1. This table or related slice is empty.
    2. COplease, please google "many to many" and set aside some time to rewrite your database scripts
      singulars
      1. POBest way to scale data, decrease loading time, make my webhost happy
      1. USDrew
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload