Database efficiency - table per user vs. table of users

sql database processing-efficiency
Yuval A. picture Yuval A. · Sep 25, 2011 · Viewed 13.7k times · Source

For a website having users. Each user having the ability to create any amount of, we'll call it "posts":

Efficiency-wise - is it better to create one table for all of the posts, saving the user-id of the user which created the post, for each post - OR creating a different separate table for each user and putting there just the posts created by that user?

Answer

Guffa picture Guffa · Sep 25, 2011

The database layout should not change when you add more data to it, so the user data should definitely be in one table.

Also:

  • Having multiple tables means that you have to create queries dynamically.

  • The cached query plan for one table won't be used for any other of the tables.

  • Having a lot of data in one table doesn't affect performance much, but having a lot of tables does.

  • If you want to add an index to the table to make queries faster, it's a lot easier to do on a single table.

Related questions

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More