Re: index selection

informix users bb writes: |> |> 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? Because, for the given select criteria, all these indexes are equivalent. If column_001 had all unique keys, then idx3 would be the better choice. Since none of them are unique, then from the optimizer's point of view, a partial key search on the composites is just as good as a search of idx3 (the same number of rows are going to be returned). If column_001 has unique values, make idx3 a unique index and see how the access changes. Since this is not a problem, it would not be "resolved" in 5.01. Dave