Forum Moderators: open
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!
Previously answered [webmasterworld.com] with link to documentation answer.
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?
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
Only other problem I can think of is table caching. Too many and it won't cache the tables right.
