re:choosing idx to use

}From: informix users bb <inf_bb@hermes1.sps.mot.com> }Date: Thu, 25 Mar 93 11:24:21 MST }To: informix-list@RMY.EMORY.EDU }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. } Bob Baskett When you create a composite index, the first field is also, effectively an index of its own. This was mentioned in the query optimization article here yesterday, but perhaps not specifically stated. Actually we have found in our ancient version of Turbo/SQL/4GL, that putting indexes as you have slows down the queries; we had to delete the index on _001 alone to speed up processing. Perhaps it is a quirk in our ancient versions, but it helped. Ann Barnes