Re: Foreign key indexing.

> Hello, Informixers! > > I know, it is helpful to create unique index on primary key and > duplicative index on foreign key. > If you have 5.00-on and use the referential integrity features (PRIMARY KEY and REFERENCES), the indexes you need get generated for you. > This is my question: is it useful to create indexes on each > component of the composite foreign key, if there is a composite > index on the hole key already? It depends how you are going to be querying the table. In any event the most important column is the first ('anchor') column in the index: If you have an index on a,b,c then there is no value whatsoever in having an extra index on a. It will just slow down your updates. If however you regularly sort on b and/or c without referencing a, then extra indexes on b,c and/or c,b may be useful, once again it is the anchor column which determines which will benefit you. Remember you can always use SET EXPLAIN ON an examine the sqexplain.out file to see which indexes are actually being used. akent@cix.compulink.co.uk (Andy Kent) ------------------------------------- +44 117 974 2815