Thanks Adam

Will do as you have suggested. We won't add the constraint as regionRef
/ Itemref is no longer actually unique.

Thanks for you comprehensive response :)

Rob Martin
Software Engineer

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

Wild Software Ltd



Adam wrote:

>> 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 :)
>>
>
>
> PK, FK and unique constraints create an ascending index as an
> implementation artifact. Looking at your table.
>
> * You have a new surrogate PK that will be indexed. Selectivity is
> excellent.
> * You have a declared FK on ItemRef. This will be indexed and the
> selectivity while not great is probably not too terrible either.
> * You have a declared FK on RegionRef. This will be indexed and the
> selectivity will be terrible, but Firebird 2 handles this much better
> than 1.x ever did.
> * You should declare a unique constraint on your old PK (RegionRef,
> ItemRef) unless you are allowed to duplicate records. This will be
> indexed and selectivity is excellent.
>
> Any version of Firebird will be smart enough to use the index behind
> any of these constraints. Even without the unique constraint, Firebird
> could in memory combine both FK indices and take advantage of this.
> But your decision to include or exclude this declared unique
> constraint should be based on the rules of your data alone.
>
> Adam
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>