New table for every user?

Question

Hi everyone,

I want to crate new table for each new user on the web site and I assume that there will be many users, I am sure that search performance will be good, but what is with maintenance??

It is MySQL which has no limit in number of tables.

Thanks a lot.

Answer 1

Why would you possibly want to do this? Just have one table for each thing that needs a table, and add a "user" column. Having a bunch of tables vs a bunch of rows isn't going to make your performance better.

Answer 2

To give you a direct answer to your question: maintenance will lower your enthousiasm at the same rate that new users sign up for your site. Not sure what language / framework you are using for your web site, but in this stage it is best to look up some small examples in that. Our guess is that in every example that you'll find, every new user gets one record in a table, not a table in the database.

Answer 3

Actually tables are stored in a table too. So in this case you would move searching in a table of users to searching in the system tables for a table.

Performance AND maintainibility will suffer badly.

Answer 4

This is not a good idea:

• The maximum number of tables is unlimited, but the table cache is finite in size, opening tables is expensive. In MyISAM, closing a table throws its keycache away. Performance will suck.
• When you need to change the schema, you will need to do one ALTER TABLE per user, which will be an unnecessary pain
• Searching for things for no particular user will involve a horrible UNION query between all or many users' tables
• It will be difficult to construct foreign key constraints correctly, as you won't have a single table with all the user ids in any more

Why are you sure that performance will be good? Have you tested it?