Hi,

> The table can have millions of records. The existing CPK was RegionRef,
> ItemRef.

> 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).

Firebird uses prefix-compression. Due that it helped improving performance by using RegionRef in the front.

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

FB2 can indeed use the individual indexes and combine them, but if you've many duplicates for RegionRef (which seems the
case) it's not optimal, because a lot of index-pages with the same value will be read. If "ItemRef" and "RegionRef"
together still should be unique, the best thing is creating a UNIQUE CONSTRAINT as Adam already pointed out.

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

If there are FK's on "RegionRef" and "ItemRef" then automaticly an index is created.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database with many powerful SQL features:
http://www.firebirdsql.org
http://www.firebirdsql.info

General database developer support:
http://www.databasedevelopmentforum.com

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info