Welcome to WebmasterWorld Guest from 18.104.22.168
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 oder 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?
Any info would be appriecated. Thanks!
I do mean tables
Without seeing a model of your dB, I can only guess... and my guess is that your design will prevent you from benefiting from the Relational aspects of a Relational Database...
Before commenting further, I'd be keen to see the syntax of one (or more) of your queries (perhaps a query that returns data from more than one client)
If you need to breakup your client data (like order history) create a table (table:orderhistory) then store that history row-by-row with an id that points back to the clients table?
1¦Barrys Home Repair¦433 Main St.¦555-9887¦firstname.lastname@example.org
2¦Tops Painting¦PO Box 93¦555-3320¦email@example.com
3¦Fitz Plumbing¦210 C. Street¦987-0755¦firstname.lastname@example.org
This way you can use an easy query to see that Tops Painting ordered 4 products (you can use a JOIN to get the part name using the PARTID) and they are on INVOICEID 9 (again use a JOIN to get INVOICE info).
In this model you can store millions upon millions of rows.