Hi

I am changing a table that had a composite primary key and replacing it
with a generated single index.

The table can have millions of records. The existing CPK was RegionRef,
ItemRef. Where a region is an area / location (i.e. Newyork, Washington
etc) and ItemRef is a FK reference to a single product. Data was commonly

RegionRef

1, 1234
1, 1235
1, 1236
1, 1237
2, 1234
etc

This was made worse where users have millions of items but only one region.
There are also individual index's on Regionref and ItemRef.

My question is should I create a Composite index to mimic the index
created for the old PK? There is a note with this table that the CPK
index was changed from Itemref, Regionref to RegionRef, ItemRef to
improve performance (I assume in some specific area).

I suppose the safest thing to do is just to mimic the old CPK index but
my reading and gut makes this feel wrong. Almost all queries are Where
ItemRef = x and regionref = y, is FB 2 smart enough to make use of the
individual indexes.

Also Should I drop the RegionRef index. It probably is more of an
overhead then a benefit ?

Any comments would be appreciated :)

--
Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd