Should we create new tables instead of filtering 1 large table?



  • In an application with groups and posts where each post can be in exactly 1 group, we can have the table POSTS(post_id, group_id, post_content). However, there's gonna be a lot of posts in a lot of groups and filtering for posts in 1 or more groups can take a lot of resources. Instead, I think we can just create a new table $(GROUP_ID)_POSTS(post_id, post_content) where $GROUP_ID is the group_id from the POSTS table. I don't know about the specifics but I think joining several tables is gonna be a lot faster than filtering through a lot of group_ids.

    Is this a good way to improve performance? Why or why not?



  • The "Table-per-whatever" Model almost always breaks down eventually.
    It's usually when some "Administrative" function needs to go across the 20,000 tables you've built up in one query, but the DBMS limits you to just 61 tables in any one query (MySQL).

    Any half-decent DBMS with sensibly-indexed Tables will cope with your first structure (post_id, group_id, post_content) with ease.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2