Re: two indexes with same columns = problem

It's because the optimizer considers the selectivity of the two index keys to be identical, which taken as compound keys they are, so whichever one it sees first it will choose all else being equal. If you are using 11.70, try dropping both of these and create single column indexes on col1 and on col2 separately and let the multi-index join code handle it. That may be best of all (have to test that of course). Then the engine can use one index or the other or combine the two depending on the query. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jul 6, 2012 at 3:00 PM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi, > > > Ifx 11.50 FC9X6 > AIX 6.1 > > We have a table where two columns are used for filter : > create table tst ( col1 int , col2 int); > select * from tst where co1 = 9 and col2 = 350> > Today exists an index over this two cols: > create index i1 on tst ( col2,col1);> > For a specific SQL this index is used but will be more efficient if the > cols appear in reversed order; > Suggested index: > create index i2 on tst ( col1, col2 );> > Reason: > if I execute this counts: > select count(*), 'col1' from tst where col1 = 9 > union all > select count(*), 'col2' from tst where col2 = 350 > the output is something like > 15 'col1' > 1800 'col2'> > > If I just create the second index, the engine still usinging the i1 . > If I drop both, and create i2 first and i1 after...then the engine use i2 > ... > > So... when have 2 indexes with the same columns, the engine use the index > what was created first. > I look at documentation and found nothing... anyone know if have some > explanation or is a defect? > > > Regards > Cesar > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >