Indexes

[tomL]

hello,
here is my question. if you have a table called related_to
and it has these fields:
id
active
from_class_id
from_id
type_cid
status_cid
seq_num
to_class_id
to_id
created_by
create_date
updated_by,
update_date

with this index:
create index "informix".related_to_i2 on "informix".related_to
(to_id,to_class_id,active,type_cid,seq_num,from_cl ass_id,from_id);

and you run this sql against it:
SELECT MAX(seq_num)
FROM related_to
WHERE type_cid = 3030 AND from_class_id = 74 AND from_id = 2184640
AND ACTIVE = 1

does it matter the order of the created index and the order of the
fields in the sql?

on a more general note, how do informix indexes work?
do they have to be in the same order?
what if there is a mismatch between all the columns in the index and
what is in the sql?

Thanks in advance,
Tom

[Fernando Nunes]

tomL wrote:

> hello,
> here is my question. if you have a table called related_to
> and it has these fields:
> id
> active
> from_class_id
> from_id
> type_cid
> status_cid
> seq_num
> to_class_id
> to_id
> created_by
> create_date
> updated_by,
> update_date
>
> with this index:
> create index "informix".related_to_i2 on "informix".related_to
> (to_id,to_class_id,active,type_cid,seq_num,from_cl ass_id,from_id);
>
> and you run this sql against it:
> SELECT MAX(seq_num)
> FROM related_to
> WHERE type_cid = 3030 AND from_class_id = 74 AND from_id = 2184640
> AND ACTIVE = 1
>
> does it matter the order of the created index and the order of the
> fields in the sql?
>
> on a more general note, how do informix indexes work?
> do they have to be in the same order?
> what if there is a mismatch between all the columns in the index and
> what is in the sql?
>
> Thanks in advance,
> Tom

Indexes in Informix work much like indexes in any other DB.
In general for an index to be choosen by the optimizer to solve a query there must be a condition (in the query) referencing the first index column.

In the above case, there is no condition on to_id in the query. As such it will make a sequential scan.
You can verify this by using the SQL instruction "SET EXPLAIN ON;" and looking at the "sqexplain.out" file in the $HOME of the user. You'll see the query plan.
Don't forget to update statistics after creating indexes.

Some versions of Oracle (9i I think) can eventually use an index when there are conditions on the second (and maybe others) columns of the indexes.
Take note that I mention "eventually" because ther must be some pre-conditions for this to be true:

1- The first column of the index must have very low selectivity (in which case you'd better think why it was chosen for index header)
2- The table must have very good statistics collected

and maybe others I don't remember.

Please note that this is an exceptional situation. The normal index behavior is the mentioned first. Think about the physical structure and layout of an index and you'll understand
why.

Regards.

[Red Dog Art]

:confused; Does anyone know if it is possible to create 2 seperate indexes?
I have one web host but would like to create 2 seperate entries one for a
gallery fine are page (ie: [url]www.artpage.com[/url]) and another for services (ie:
[url]www.reddog.com[/url]). Any help would be appreciated. Thanks for you time and
assistance in advance.
Sue