Welcome to WebmasterWorld Guest from 54.158.109.89

Forum Moderators: open

Message Too Old, No Replies

Impact of Too Many Tables?

     
12:59 am on Jul 20, 2009 (gmt 0)

New User

5+ Year Member

joined:July 3, 2009
posts:5
votes: 0


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 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!

2:51 am on July 20, 2009 (gmt 0)

Preferred Member

5+ Year Member

joined:May 29, 2007
posts: 578
votes: 0


should I expect any issues around how fast MySQL can access the data?

If, by tables, you mean records (a.k.a. tuples):
No :)

However, if you really do mean tables, then I suggest you redesign your database

3:00 am on July 20, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member ogletree is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 14, 2003
posts:4307
votes: 35


As long as you mean records and not tables that is not a problem. When you do start to get big a lot of companies use sharding.
6:08 am on July 20, 2009 (gmt 0)

New User

5+ Year Member

joined:July 3, 2009
posts: 5
votes: 0


I do mean tables. A new small table is created for each new client, each table only having about 20-40 rows...

What kind of issues can I encounter with thousands of tables? I can't find any documentation that outlines risks and issues.

Thanks!

6:47 am on July 20, 2009 (gmt 0)

Preferred Member

5+ Year Member

joined:May 29, 2007
posts:578
votes: 0


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)

5:44 pm on Sept 9, 2009 (gmt 0)

Full Member

10+ Year Member

joined:June 24, 2004
posts:202
votes: 0


Question: Why do you create a new "Table" for each client? Why not create 1 table (table:clients) then insert customer data into this table, row-by-row.

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?

EXAMPLE:
TABLE:clients (id¦clientname¦address¦phone¦email)
1¦Barrys Home Repair¦433 Main St.¦555-9887¦info@domain.com
2¦Tops Painting¦PO Box 93¦555-3320¦top@domain.com
3¦Fitz Plumbing¦210 C. Street¦987-0755¦info@domain.com

TABLE:orderhistory (id¦clientid¦partid¦invoiceid)
1¦2¦834¦9
2¦2¦2211¦9
3¦2¦0093¦9
4¦2¦832¦9
5¦1¦733¦10

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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members