Re: How can I tell infomix which is the best index to use

Basically, you need to try several things. First, assuming this is a 7.x or higher engine, update statistics (medium or high) for these two columns. Second, turn explain on and run the query with the date-time filter only. What is the cost? How much did it increase of the first query? Third, run the query with the additional filter and look at the cost. Did the optomizer compute a higher cost? If the selectivity is too high, the optomizer will elect to scan the table. Remeber that data pages are contiguous, but the index pages are not contiguous. You may have to perform a lot of random I/O to return the desired data, versus using lite scans to scan the table. 4th, Try the composite index as suggested below. 5th, If this is a DSS environment, consider the upgrade to 8.2x. This will allow the use of multi-index scans. Noel Teis wrote: > art wrote: > > > This is basicly what I have > > one table with two fields indexed > > > > date_time and acct_number > > > > when I run the following querry it takes to long > > > > select * from table > > where date_time > "1998-04-02 00:00:00" and acct_number = 123;> > > > if I take the "and acct_number = 123" off it returns immediately > > > > any way I can tell informix to user the date_time index and not the acct_number > > index and still check the acct_number value > > > > note just sample data to make my point that it is using acct_number > > Try a multi-key index > > create index table_1 on table(date_time, acct_number); -- David Proksch Advanced Technology Group Informix Software, Inc. #include <disclaimer.h>