Re: index selection
> i think this may have been posted previously, but here it goes.
> how does informix sql determine which index to use when executing
> a query? the problem we are having is we have a table that has
> several indexs which include a given column (lets say column_001);
> all but one are composite indexes as follows (as listed by the
> isql - tables - info - indexes ):
>
> create index idx1 on tab1 (columne_001,column_002);
> create index idx2 on tab1 (columne_001,column_009);
> create index idx3 on tab1 (columne_001);
> create index idx4 on tab1 (columne_001,column_005);>
> when you execute the following query, the idx1 index is used:
>
> select * from tab1 where column_001 >= somevalue;>
> i know that if i drop the idx1 and idx2 indexes isql will use the
> idx3 index.
>
> why is this? anyone else have this problem? has it been resolved
> in 5.01?
> | . .
> Bob Baskett | ... ...
Bob,
It seems to me that this is working OK. It is choosing the first
index that will do the job. For the query you specified it can use
any of the indexes to get the job done at about the same efficiency.
This is based on the assumption that index reading doesn't traverse
every branch and leaf to get to the next col001 value but can break
between col001 values at a higher level in the tree. This assumption
may be wrong!!
Informix did have a problem in version 4.0 which I never investigated
whether it had been solved because for my application I found a work
round. This is that if you specify an order by col001, col005 it
would still use idx1 as it finds the first index with col001 as the
primary column and assumes there are no other indexes that would
better match the order requested.
The work round was the order in which I generated the indexes. As the
other indexes where used for direct where clause look ups I just
created the order by index first.
Cheers - Jim
--------------------------------------------------------------------
Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM
Company: DHL Systems Inc Phone: (415) 358-5911 (Work)
Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home)
San Mateo, CA 94402 Fax: (415) 571-6429
--------------------------------------------------------------------