MySql - Large Single Table or Multiple Small Tables

Question

I am in the process of building a PHP/MySql database application which could have heavy usage on certain tables.

Three tables have the potential to have lots of UPDATEs executed - here is some information about these tables

1. each user will have a maximum of 200 rows per table
2. each row can be as large as 250k
3. potential user base may be 5,000 users

The broad question I have is what is the max # tables per MySql database (on a Linux server). I am also interested in the pros and cons of taking the approach of assigning a table per user instead of a single table which all users share.

Answer 1

The number of tables is limited only by either the number inodes or the physical space on disk.

Without knowing more about what you're doing though it's hard to give a great opinion. Generally I would say that a 1,000,000 row table isn't that big of a deal for MySQL as long as you have proper indexes. IMHO that's a better solution than having a table for each user (the system can do it but I think it ends up being a maintenance nightmare).

Why do you need 250k per table? What are you storing that is so large in each row? Explore splitting that data into other tables.

Generally good database design puts categories of data into tables rather than using tables as rows.

Answer 2

MySQL doesn't impose max table limits. The real limitation is going to 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 every user, for a myriad of reasons - you want your data IN tables.

Put an index on your user ID table and you'll be fine.

Answer 3

One table is definitely preferable from a design and programming point of view. I would consider the multi-table sharding approach a hack that you should only consider for performance reasons after developing and testing the "correct" design.

The UPDATEs will hurt you if 1) your table is extensively indexed or 2) they involve a lot of row-rewriting / moving (something I don't know the details of in MySQL).

Answer 4

The max # of tables per database is limited based on your filesystem. That number corresponds to the number of files allowed in a directory.

Creating that many tables seems like a maintenance nightmare. If you ever need to change anything about your tables, you need to do so for every single user. That could hours or days. Keeping all your user data in the same table with a good index on the user id will get you much further.