Should we create new tables instead of filtering 1 large table?
In an application with
posts where each
postcan 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
POSTStable. 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
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.