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

In your mail you haven't specified how you found what index is being used. Did you use set explain? Have you been updating statistics on the table? Have you updated statistics high on the date_time and acct_number? I believe the reason why the acct_number index may be "winning" is because of the equality check. You may be able to fool the optimizer by specifing a condition like acct_number > 122 and acct_number < 125 (just an idea). You always have the option of dropping the index on acct_number but by judging by it's name that doen't seem like a wise thing to do. Let me know how it goes, Bill } } 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 -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison emails: ennis@ssax.com ennis@starnetusa.com