Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Structure for storing posts

  1. #1
    Join Date
    Mar 2011
    Location
    N 11░ 19' 0.0012 E 142░ 15' 0
    Posts
    1,530
    Thanks
    41
    Thanked 89 Times in 88 Posts
    Blog Entries
    3

    Default Structure for storing posts

    Note: This isn't so much a sql coding question as a structuring question.

    My question is this - how do the big guns (example: f a c e b o o k) store the peoples posts.

    Would they have one big table and every post that is made, gets stored in their?
    Or would they have a database for each person and have a table in it for all the posts they've made.

    Sorry if I haven't been clear enough,
    Keyboard1333
    Last edited by keyboard; 05-16-2012 at 05:01 AM.

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    I don't know, and that's an interesting question. But I'd imagine that they have special software for at least some of it. It probably works like a cloud server but for a database. I don't know if it copies every bit of info or just in chunks, but it probably can search around various chunks at the same time. It seems like mixing both methods would be most efficient, if you went through the trouble of programming all of that yourself-- and they certainly have the resources.

    It could always be a very very well maintained flat file system. Part of that might be used for things like posts that can't be searched (right?), just something displayed on screen. For usernames and thinks like that (that you can search) it would be in the main database.

    Has anyone else seen inside any of the sites like that?


    A similar question would be how google stores its information, but that's just a completely different situation without the social piece (so different databases can be queried in smarter ways, probably). And lots of caching of queries.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. #3
    Join Date
    Mar 2011
    Location
    N 11░ 19' 0.0012 E 142░ 15' 0
    Posts
    1,530
    Thanks
    41
    Thanked 89 Times in 88 Posts
    Blog Entries
    3

    Default

    I was thinking maybe you could store them all in one huge table then wondered if it would be to slow????

  4. #4
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    It would be. Databases can get pretty big, but to store all of facebook in one table would be ridiculous.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  5. #5
    Join Date
    Mar 2011
    Location
    N 11░ 19' 0.0012 E 142░ 15' 0
    Posts
    1,530
    Thanks
    41
    Thanked 89 Times in 88 Posts
    Blog Entries
    3

    Default

    Could you store all the posts in one table on one day? Then create a new one the next day and so on?

  6. #6
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,740
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Makes sense. Good thought keyboard1333.
    To choose the lesser of two evils is still to choose evil. My personal site

  7. #7
    Join Date
    Mar 2011
    Location
    N 11░ 19' 0.0012 E 142░ 15' 0
    Posts
    1,530
    Thanks
    41
    Thanked 89 Times in 88 Posts
    Blog Entries
    3

    Default

    Quote Originally Posted by james438 View Post
    Makes sense. Good thought keyboard1333.

    Really??? oh, okay . Would there still be to many posts per day? (Of course depending on the popularity of the site).

    Does anyone know about how many values a mysql table could hold???

    Keyboard1333

  8. #8
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    The size limit is usually imposed by the operating system, not MySQL itself.

    For example, if you are using the MyISAM driver, you can have table (table, not DB) sizes "up to 4GB by default (256TB as of MySQL 5.0.6), but this limit can be changed up to the maximum permissible size of 65,536TB"
    [http://dev.mysql.com/doc/refman/5.0/...ize-limit.html]

    Yeah, you read that right. Sixty-five Thousand Terabytes.

    As you imagine, though, it probably wouldn't run very efficiently, and I don't know what computer/OS would support a file that large.

    About the FB question: interesting read.

    About your question:
    I would suspect that "posts" are broken up across several different tables.

    The actual text of the post might be in one table, which user it belongs to in another, who likes it in another, and so on. Most of it would be "relational" information: just index keys that point to where to look. Then, there would need to be a lot of indexing, and caching becomes essential long before you get to "FB" size.

  9. #9
    Join Date
    Mar 2011
    Location
    N 11░ 19' 0.0012 E 142░ 15' 0
    Posts
    1,530
    Thanks
    41
    Thanked 89 Times in 88 Posts
    Blog Entries
    3

    Default

    Quote Originally Posted by traq View Post
    and caching becomes essential long before you get to "FB" size.
    Sorry if this is a dumb question... but what do you mean by that?

  10. #10
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    instead of querying the DB every single time, query once and save (cache) the result. (Memcache is a common caching tool.)

    It saves a lot of overhead (DB connections, queries, etc.), especially for common/frequent queries.

    The cache can be cleared regularly, or left for quite some time in the case of static data.

  11. The Following User Says Thank You to traq For This Useful Post:

    djr33 (05-11-2012)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •