Re: two indexes with same columns = problem

Hi Art. Thanks for your answer. We don't have 11.70 here yet... neither for test... Please, if possible, can you clarify what means the "selectivity" ? For me , there is no way of this two indexes have the same selectivity , one way (index i2) they filter 15 rows at first step on the second way (index i1) they filter 1800 records first... where will have bigger work to do... The optimizer should not use the statistics of each field to decide how filter first? Or I misunderstand something? ________________________________ De: Art Kagel <art.kagel@gmail.com> Para: Cesar Inacio Martins <cesar_inacio_martins@yahoo.com.br> Cc: ids@iiug.org; informix-list@iiug.org Enviadas: Sexta-feira, 6 de Julho de 2012 16:33 Assunto: 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 > >