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

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 (AT) yahoo (DOT) com.br> wrote:

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 stepon 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 (AT) gmail (DOT) com>
Para: Cesar Inacio Martins <cesar_inacio_martins (AT) yahoo (DOT) com.br>
Cc: ids (AT) iiug (DOT) org; informix-list (AT) iiug (DOT) 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, sowhichever one it sees first it will choose all else being equal.* If youare 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 (AT) yahoo (DOT) com.br> wrote:

Hi,

The optimizer evaluates the "selectivity" of the entire key for each
index. By selectivity I mean how many rows are eliminated by searching for
the filter values in this index. Yes, col2 eliminates all but 350 rows
while col1 eliminates all but 9 rows so col1 has "better" selectivity,
however, the optimizer cannot tell from the distributions how many of the 9
rows with the col1 value presented as a filter also have the col2 value
presented so the only thing it knows for sure is that using either index it
will return at most 9 rows. So the selectivity of both compound keys is
identical - all but 9 rows PROBABLY eliminated.

The internal efficiency of using one index over the other with two identity
filters (col1=<value1> and col2=<value2>) is also about equal. In the
first case it finds the one leaf node that contains the given col2 value
and the unique col1 value combined in that order and finds somewhere
between zero and nine rowids. In the second case it does the exact same
thing except that it combines the columns in the opposite order still
resulting in a single leaf node with from zero to nine rowids in it.
Unless one index has more levels than the other, they really are the same.
IFF you were making range scans with inequality filters then one index
would become more efficient than the other.

Now, if the final result is indeed fewer than nine rows in the general case
then 11.70's multi-index join will be more efficient. Otherwise, the
simpler code path of using the compound index, either one, would be.

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 4:13 PM, Cesar Inacio Martins <
cesar_inacio_martins (AT) yahoo (DOT) com.br> wrote:

Oh, forgot to mention, if we were comparing two keys of different lengths,
then the width and depth of the indexes would come into play and the
optimizer would have that efficiency information to use to select one index
over the other (so if one index were (col2, col1) and the other were (col1,
col2, wide_char_col3) ). The shallower index would tend to require fewer
IOs to get from root to leaf and so would be selected given identical
selectivity. In your case, the keys are identical length and identical
columns in different orders so the depths of the two indexes will be
identical also. Without any clues that one index is actually better than
the other, the optimizer just chooses the one it finds first.

Have you done any performance timings to determine that one version of the
index is indeed better than the other?

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 4:27 PM, Art Kagel <art.kagel (AT) gmail (DOT) com> wrote: