Re: Questions on Indexes

On Wed, Apr 28, 1999 at 12:52:58PM +0000, Obnoxio The Clown wrote: > 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. > You don't need, but you can: just create the index BEFORE creating the foreign key. If informix find a index on columns in foreign key it will use it and will not create another one. If you will have to use optimizer directives this explicit index (of course you should give it a name) could help you. Best Regards, Octav -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com