Two index query optimization problem (fwd)

} I'm having some problems with Informix over optimizing my query. } I have a table with > 500000 rows and I can't get Informix to } use what I deem to be the "correct" index. } } There are two indexes on the table: } } index1: unique index on A, B; } index2: unique index on C, D, A, B; } } The problem I'm having is that I can't run a query with A and B mentioned } in the WHERE clause that will use index2. I suspect that the optimizer } is looking at the query and saying; hey, I got A, I got B, I've got a unique } index1 on both A and B. Look no further, I am set dudes and dudettes, and } I am outta here! (optimizers talk like that you know). } } This would be fine if my queries were of the form: } } WHERE C=value AND D=value A=value AND B=value; } } However, one of my queries is of the form: } } WHERE C=value AND D=value A>value AND B=value; } } so it doesn't _really_ have a value for A. I do need to have both } indexes for performance reasons. I don't think that setting optimization } to low is an option in this case since it effects the whole database } back end and not just the single query. We are running Online V 4.10.UF2, } SQL V 4.00.UG2 on a Pyramid MI Server. } Is c,d unique? if so change index two to be just c,d. If it isn't try making c,d a dupe (and drop the a,b part). Maybe the optimizer isn't all that screwed up, try dropping index1 and seeing how long the query takes versus how long it takes now. j. _____________________________________________________________________________ Jack Parker - Contractor | Hewlett Packard, BSMC Boise, Idaho, USA| Put it on the IN pile... jparker@hpbs2651.boi.hp.com | (208) 323-5388 (W) (208) 384-1623 (H) | _____________________________________________________________________________