Questions on Indexes
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. 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? 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'"? 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. I would appreciate the help from members of this news group. Thank you. Jon
When you specify a foreign key for a table, a non-unique index will
implicitly be created that contains the columns in the foreign key, * if
and only if* the index does not already exist. In your case, since the
index was created as a result of designating a foreign key, your attempt
to explicitly create the index failed (there can only be one index defined
on a column or set of columns).
Yes, the index created via the foreign key statement is available to to
optimizer to use in its query plan.
So, the following will implicitly create a non-unique index on the column
ssn:
create table proj_emp
(
project_id integer not null,
ssn_no char(9) not null,
foreign key (project_id) references project,
foreign key (ssn_no) references emp
);
"Jon M. Roe" wrote:
> 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.
>
> 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?
>
> 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'"?
>
> 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.
>
> I would appreciate the help from members of this news group.
>
> Thank you.
>
> Jon
Thank you all for answering my questions in a very timely manner. Jon