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

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).