Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

New table for every user?

Tags:

mysql

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.

like image 512
eomeroff Avatar asked May 30 '10 21:05

eomeroff

People also ask

Should I create new table for each user?

It would be best to create different tables for each entity. For example, you could create one for all users, one for all bills/purchases, and one for all products. You can connect the user table and billing table by a user ID.

How do I add a new table to an existing table?

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected.

What are the 5 basic components of table?

Main components of table are:1:Table number 2:Title 3:Head note 4:Stub head 5:Stub entries 6:Column heading 7:Body of the table 8:Footnote 9:Source. Was this answer helpful?

How many types of table creation are there?

There are three types of tables: base, view, and merged. Every table is a document with its own title, viewers, saved visualizations, and set of data. The data in each type of table has different properties. base: A table.

1 Answers

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?

like image 121
MarkR Avatar answered Sep 24 '22 14:09

MarkR
Sign in to Comment

Related questions

Case when then, but with AND condition inside when and before then
Use Composite Primary Key as Foreign Key
How to iterate over Yii CActiveDataProvider object?
Start MySQL Server as a service (Win 8)
How to import your existing table structure in EER diagram using MySQL Workbench?
Connection fails with mysql using GoLang
Laravel created_at return object in place of date format in database? [closed]
Get the year out of timestamp sql
mysql avg on conditional
Node.JS reading BLOB from mysql
How to add brackets around WHERE conditions with Laravel query builder
Laravel DB::transaction not rolling back on exception
If condition with group_concat in mysql
SQL join on junction table with many to many relation
Where to store SQL commands for execution
SQL: How can I update a value on a column only if that value is null?
mysql_config_editor --login-path=local not working
export large database mysql phpmyadmin
Regaining access to lost MySQL password for PHPMyAdmin on WAMP
Use MySQL to determine whether today is a user's birthday

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!