MySql - Large single table or several small tables

Question

I am creating a PHP / MySql database application that can be heavily used in certain tables.

Three tables have the potential to do a lot of UPDATE - here is some information about these tables

• each user will have no more than 200 rows per table
• each row can reach 250 thousand
• potential user base can be 5,000 users.

The broad question I have is what are the max # tables for the MySql database (on a Linux server). I am also interested in the pros and cons of the approach to assigning a table to each user instead of a separate table that all users use.

Answer 1

The number of tables is limited only by the number of indexes or physical disk space.

Not knowing more about what you are doing, although it is difficult to give an excellent opinion. Generally, I would say that a table of 1,000,000 rows is not a big deal for MySQL if you have the appropriate indexes. IMHO, that a better solution than having a table for each user (the system can do this, but I think it ends with a maintenance nightmare).

Why do you need 250 kbps per table? What do you keep on each line so big? Consider splitting this data into other tables.

In general, a good database design puts data categories in tables, rather than using tables as rows.

Answer 2

One table is certainly preferable in terms of design and programming. I would consider a multi-table approach for hacking, which should only be considered for performance reasons after developing and testing the “right” design.

UPDATES will harm you if 1) your table is heavily indexed or 2) they include a lot of line rewriting / moving (something that I don't know in MySQL).

Answer 3

The maximum number of tables per database is limited based on your file system. This number corresponds to the number of files allowed in the directory.

Creating many tables seems like a nightmare to maintain. If you ever need to change something about your tables, you need to do this for each individual user. It may be several hours or days. Storing all your user data in the same table with a good index on the user ID will give you much more.

Answer 4

MySQL does not set maximum table limits. The real limitation will be your OS. MySQL stores each table as a separate file, so having 5,000 tables instead of 2 or 3 is not so good.

I would definitely not recommend creating a table for each user for many reasons - you need your IN data.

Put the index in the user ID table and everything will be fine.