Splitting a large table.

[Dave Bagler]

Hi,
I've read a few posts in various forums about whether or not it's better to split a large table into smaller tables. They have some good points but none have really answered my question.

I have a database where I need to store customer records. Each customer record is associated with a region. There are just over 100 regions. Each region has around 100,000 customer records that need to be stored.

I can do this using a region table (with about 100 records) and a customer table (with about 10,000,000 records.)

The issue is that it is very rare that a query needs to be done on customers from more than one region. Also data is almost never deleted or inserted but is often updated and queried.

I was wondering if it would be better to keep all of the customer records in one table with a Foreign key field for regions or make a separate table for the customers in each region?

UPDATE: I know that using version 5.1+ I can partition the table but I currently use version 5.0 is partitioning worth the upgrade?

Thanks!



Edited 1 time(s). Last edit at 03/10/2009 08:58AM by Dave Bagler.