Re: Primary key, Foreign key and indexes in Informix

----- Original Message ----- From: "KalpanaPai" <kalpanapai@hotmail.com> To: <informix-list@iiug.org> Sent: Tuesday, April 27, 2004 9:11 PM Subject: Primary key, Foreign key and indexes in Informix > Dear All, > > We r using informix 9.21 on solaris. > We have a small database of 1.5GB with 100 tables. > > I have staretd analysing the appliaction queries which have written > in the past. I have few questions regarding PK,FK and index creation > in informix. > > 1. When i create a Primary key it creates an index automatically. Do i > need to create explicit index for better performance? When you create an index you can tell the engine where to put it. When you use the primary key syntax - the engine decides where to put it. If you create a unique key that matches the primary key requirements - the engine will use that index to satisfy the primary key - hence it is generally best to first define you own unique index which the engine can use to satisfy the primary key. > > 2. When i create a foreign key it also creates an index, do i need to > create an index on the foreign key also explicitly , if it is used in > where cluase etc. Ditto. fer shure. > > 3. Will it imprve the performance of the queries if we create a > explicit indexes . Perhaps (assuming the same line of questioning). Index location is more of a storage location consideration and spindle usage consideration - which is generally better known by the DBA than by the engine. The index itself is a b(+)-tree, so you are going to navigate the b-tree whenever you try to read it. You want that tree to be as tight as possible and removed from potentially contentious situations - hence why we bother to take control over where it goes. > > 4. If i am using 4 columns in one query which runs very frequently, > and one of the same column i use in another qury, and another column > in another query. In this case is it better to create a composite > index on all 4 columns , and separate indexes on each column? > It depends. (;-)). The first column (or set of columns) of a composite index can be used by queries that do not fully qualify the entire index contents. So if I have an index on columns a,b,c and d, this will be used to satisfy queries where a,b,c,d are all spelled out - or where a and b are spelled out, or just 'a'. It will NOT be used when only 'b' is spelled out (or just c or just d - or some combination thereof). > Any suggestions highly appreciated.. > Thanks in Advance > Kpai > sending to informix-list