0

big problem, can't solve it by myself, in my application i want that every user will have 'x' amount of friends, list of friends, the problem is if i create 2 tables, 'users' , 'friends', the first table will hold username and uid, second table will hold the relationship between them, like each row will have 3 columns, 'user1',user2','id'

but what happens if i have 1 million users, each with 200 friends, the second table will have 200 millions rows...

i tried to think about splitting the tables, so i create 100 table for each 10000 users friends , limit friend count to 200, and then each table will be 2 million row max. ill create each table dynamically when 1 table reached certain amount of row count wich ill monitor in another table.

another possible solution was to store them in array, to serialize the array, so it will be text and store in in 1 column in the database, this is more simple, but the array can be big with 200 friend list, and i afraid it will be costly on memory usage.

another solution i had, is to create 200 columns, 'friend1','friend2',...,'friend200'.

and then with simple SELECT * to tale all the list of friends from the columns, the problem, is that it will be also costly on memory probably, and also how can i pick only the columns that are not empty/null. and also what happens if i need to delete a friend from the friends list... how can i identify the right columns, zero it, and then, enter new friend intead...

Noobit
  • 11
  • 2
  • 4
    Worry about getting a million users later. If you get that size, you'll have an engineering team and the ability to scale up to massive server clusters for which 200,000,000 rows is just fine. – ceejayoz Feb 19 '15 at 13:00
  • 2
    So, you have 200 million rows. MySQL can handle that (for at least listing all the friends for a given person). Learn about indexes and partitioning, which are used to improve performance on large tables. – Gordon Linoff Feb 19 '15 at 13:01
  • i'm not sure 1 million users will give me that much revenue for that :) – Noobit Feb 19 '15 at 13:01
  • 1
    Please, my friend , read a data modeling book. – Joel Hernandez Feb 19 '15 at 13:02
  • http://stackoverflow.com/questions/385506/when-is-optimisation-premature – ceejayoz Feb 19 '15 at 13:02
  • 2
    If you have 1 million users, each with 200 friends, give Zuckerberg a ring, and then go live on a yacht somewhere. – Strawberry Feb 19 '15 at 13:05

0 Answers0