homepage Welcome to WebmasterWorld Guest from 54.198.224.121
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Impact of Too Many Tables?
SageDog




msg:3955483
 12:59 am on Jul 20, 2009 (gmt 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!

 

lavazza




msg:3955517
 2:51 am on Jul 20, 2009 (gmt 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

ogletree




msg:3955518
 3:00 am on Jul 20, 2009 (gmt 0)

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.

SageDog




msg:3955572
 6:08 am on Jul 20, 2009 (gmt 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!

lavazza




msg:3955575
 6:47 am on Jul 20, 2009 (gmt 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)

blaketar




msg:3986761
 5:44 pm on Sep 9, 2009 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved