(no subject)

I am new to the Informix SIG, but let me say that I have already read some interesting correspondence and find the service very valuable. Help please, and thanks in advance... I am trying to determine whether there is a certain order in which Informix-SQL 4.0 performs indexed queries, and if so is this order alterable or controllable without recreating tables? No problem arises when querying a single indexed field. However, access time seriously deteriorates when a query is performed by conjoining two (or more?) indexed fields. For example, given two indexed fields f1 and f2 where f1 is an index for only a few entries and f2 is an index for many entries, then there is an interminable wait for the following select to complete: ... SELECT * FROM table-name WHERE f1 = somevalue AND f2 = somevalue ... Looking at Informix's TMP tables seems to indicate that all f2 entries are being selected first and then f1 entries are being selected next (regardless of the ordering in the AND clause). The only reason I can find for f2 entries being selected first is that when a table is originally indexed then which ever index one creates later on is used first by Informix. If this is the case, then great care must be take in the physical ordering of CREATE INDEX statements. If my problem is indeed one of the order in which I originally created indices, then a more judicious ordering of CREATE INDEX statements poses no problem AS LONG AS the Informix engine consistently (and not merely FOR THE MOST PART!) performs upon the basis of LAST INDEXED, FIRST QUERIED (LIFQ). Thus, what I would like to know first is whether this LIFQ scheme is true and if so is it CONSISTENTLY true for Informix? If the above observations are correct, then Informix cannot be expected to know in advance which indexed field contains more or fewer entries; so I would also like to know whether anyone has found some way (other than by an inverse ordering of CREATE INDEX statements) to specify which index one wants to be use first? If there is a way to specify which indexed field should be searched first, then one could change on-the-fly the priority of indices--a feature which would provide a tremendous amount of control over queries! On the other hand, if the above LIFQ approach is either incorrect or inconsistently performed by the Informix engine, then a possible solution to the problem would be to query using f1, put the results into a temporary table, and then query from that temporary table using f2. This, though, seems to negate the value of being able to perform conjunctive indexed queries for one would be in effect manually performing a feature of Informix. Comments, suggestions, solutions would be greatly appreciated, ..Chris Lewis lewis@fredonia.bitnet