Maximum Number of Tables in a mySQL Database?

[SageDog]

Hi,

I have a MySQL database that's been running well for a few years but is undergoing huge growth right now.

In this DB, I have one big table of clients (name, contact info, etc). When a client signs up, a table is created specifically for their order history, and occasionally a second table may be created for them to track other info. These tables are both very small (20-40 rows, 100 chars each row).

What this means is that I currently have almost 3000 tables in the database, with the possibility of 6000 new tables added each year... Am I going to run into trouble when the database grows toward 10,000+ tables? Is there a limit of tables and can it be expanded? And, should I expect any issues around how fast MySQL can access the data?

Re-design of the DB isn't really an option at this point... unless we have to... Is there anyone out there who can tell me what happens when there's 10,000+ individual tables in a DB?

Any info would be appriecated. Thanks!

[rocknbil]

It's going to really be specific to your system, it's the same as asking, "how many videos can my 120 GB hard drive hold?" Depends on the size of the tables, capacity of the server, etc.

Previously answered [webmasterworld.com] with link to documentation answer.

[SageDog]

Thanks rocknbil!

These tables that are being added are very small (20-40 rows, 100 characters per row)- they're a short record of a customer's specific order info and for access to their purchased products. So, we're talking very little space.

Our server can be updated if need be- were you mostly referring to disk space or is there another factor?

[rocknbil]

Well, see the documentation in the above link - I think it's multiple factors, including memory, but largely yes, it's disk space and the rumored limitation mentioned.

[lammert]

If I remember correctly, the MyISAM storage engine uses one file per table with the name tablename.MYD which is continuously opened by MySQL. Other storage engines like InnoDB store all tables in a few central files. Assuming you are using the default MyISAM storage engine and with your numbers of 10000+ tables vs. 40 rows per table, I would guess that concurrent open files will be the first limit you hit, not disk space or memory space.

If you have access to the linux command level of this server, you can get an idea of the files opened by mysql with a command similar to:

lsof ¦ grep mysql

[Frank_Rizzo]

Can't see why you don't have one master table rather all those small ones.

Only other problem I can think of is table caching. Too many and it won't cache the tables right.