[ic] [ic]/[mv] Large Product tables

[Mike Heins]

Quoting Barney Treadway (barney@ecomshare.com):
> Greetings all,
> 
> We're looking at a mysql backed interchange with estimated 3-5 million
> product records in short order. There's a natural break up in products to
> allow us to create 50-100 product tables rather than a single massive table.
> This would facilitate quicker updating etc from those editing/adding
> products (which we do via php). Does anyone have any input (advice,
> concerns, suggestions, threats, etc) as to interchange's ability to do
> common loops and search regions on either 1) a large number of tables
> (50-100) or 2) tables with millions of records?
> 
> Any responses would be greatly appreciated.
> 

There is really no way to do this well. It would require 50 queries
instead of one. And 50-100 is a lot of table objects to maintain. It is
too many ProductFiles for performance to be maintained in most cases.

It would be much faster to index a large table on a field and display it that
way. This is a logical break as well -- MySQL can do very fast selects
on a field = 'string' indexed field.

One possibility is to place only sku, category/owner pointer, and
short description in the large table and break the subsidiary stuff
into separate tables that would then be selected via the category
pointer. This would be a fast table to update, while the separate
tables that contain the extended data could be broken into pieces.
Still, the more tables you have the more overhead. If you want to
place a lot of load on the catalog you would have to encode your
display routines in embedded Perl to get real performance.

-- 
Akopia, Inc., 131 Willow Lane, Floor 2, Oxford, OH  45056
phone +1.513.523.7621 fax 7501 <heins@akopia.com>

Be patient. God isn't finished with me yet.  -- unknown