Re: Questions on Indexes

From: jroe@erols.com (Jon M. Roe) > >I have a couple of naive questions about the behavior >of indexes and keys in Informix. > >Situation: > >I have Informix V7.30 on HP-UX 10.20. > >I have Table X with >a primary key defined as columns (A + B + C + D) and a >foreign key defined on column (A) referring back to a >controlling look-up table, Table Y. > >I require many queries >based on column A and many queries based on column D in Table X >and Table X is very large. > >It was suggested to me that I create two indexes, one on >column A alone and the second on column D alone to >optimize these queries. Informix created the index on column >D alone with no problem. However, Informix would not create >the index on column A alone and returned with error -350 >to the effect that this create index command cannot be processed >because "an index on the same column or combination of columns >already exists. At most two indexes can exist on any combination >of columns." > >Questions: > >1) I already have two indexes that include column A, correct? One >that is composed of the primary key columns and one that is composed >of the foreign key column. Yes. Informix implements referential integrity via indexes. >2) Do either of these indexes (from primary key and foreign key) >serve the purpose of a plain index on column A to optimize searches >using column A? Both of them do, funnily enough. The primary key can act as an index on A, A + B, A + B + C and A + B + C + D, the foreign key also acts as an index on A. >3) Specifically, is the foreign key index, that is based on column A, >used to perform queries such as "select * from Table X where >A = 'abc'"? Yes. >4) So, in my case, do I need to try to create my own index on column >A for any purpose, that is, would it help? Of course it appears that >I can't do this unless I get rid of the foreign key. No, you don't need to. It won't be any faster and will make it impossible to implement referential integrity. HTH. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com