Tom

In the specific example you quote the index will be used to directly retrieve
the required records, order of where clause does not matter.
What will stop a direct index read and cause an index scan (full or partial)
is if not all the indexed fields, in sequence, are used in the where clause
(ie where col1 and col3 would be OK, where col1 and col4 would cause in index
scan on all col1 matches).
Also if you use an IN clause, or BETWEEN or MATCHES (especially on any
other than the last column in the index) you will get an index scan (or even
table scan if the optimiser thinks this would be quicker).
SET EXPLAIN ON; is your friend.

Keith

-> -----Original Message-----
-> From: tomcaml@yahoo.com [mailto:tomcaml@yahoo.com]
-> Sent: Wednesday, March 02, 2005 5:28 PM
-> To: informix-list@iiug.org
-> Subject: indexes
->
->
-> hello
->
-> stull fuzzy on index retrieval and the order of the indexed
-> fields....
->
-> if i have a table with these columns indexed (col1, col3, col4)
-> and the sql says col1, col4, col3 does the index still get used or
-> does it stop at col1 because of the order?
->
-> i assume always using the exact order of fields in the sql
-> to match the
-> indexes is the way to be correct but am unsure about different orders
-> or if the sql has two out of four included but they are either in the
-> same order or ordered differently.
->
-> thanks in advance!
->
-> tom
->

************************************************** ********************************
This message is sent in strict confidence for the addressee only. It may
contain legally privileged information. The contents are not to be disclosed
to anyone other than the addressee. Unauthorised recipients are requested
to preserve this confidentiality and to advise the sender immediately of any
error in transmission.
This footnote also confirms that this email message has been swept for the
presence of computer viruses, however we cannot guarantee that this message
is free from such problems.
************************************************** ********************************


sending to informix-list

Thanks.
Are you thinking an IN is slower than a NOT IN, given all other things
are equal ?




Simmons, Keith wrote:
> Tom
>
> In the specific example you quote the index will be used to directly
retrieve
> the required records, order of where clause does not matter.
> What will stop a direct index read and cause an index scan (full or
partial)
> is if not all the indexed fields, in sequence, are used in the where
clause
> (ie where col1 and col3 would be OK, where col1 and col4 would cause
in index
> scan on all col1 matches).
> Also if you use an IN clause, or BETWEEN or MATCHES (especially on
any
> other than the last column in the index) you will get an index scan
(or even
> table scan if the optimiser thinks this would be quicker).
> SET EXPLAIN ON; is your friend.
>
> Keith
>
> -> -----Original Message-----
> -> From: tomcaml@yahoo.com [mailto:tomcaml@yahoo.com]
> -> Sent: Wednesday, March 02, 2005 5:28 PM
> -> To: informix-list@iiug.org
> -> Subject: indexes
> ->
> ->
> -> hello
> ->
> -> stull fuzzy on index retrieval and the order of the indexed
> -> fields....
> ->
> -> if i have a table with these columns indexed (col1, col3, col4)
> -> and the sql says col1, col4, col3 does the index still get used
or
> -> does it stop at col1 because of the order?
> ->
> -> i assume always using the exact order of fields in the sql
> -> to match the
> -> indexes is the way to be correct but am unsure about different
orders
> -> or if the sql has two out of four included but they are either in
the
> -> same order or ordered differently.
> ->
> -> thanks in advance!
> ->
> -> tom
> ->
>
>
************************************************** ********************************
> This message is sent in strict confidence for the addressee only. It
may
> contain legally privileged information. The contents are not to be
disclosed
> to anyone other than the addressee. Unauthorised recipients are
requested
> to preserve this confidentiality and to advise the sender immediately
of any
> error in transmission.
> This footnote also confirms that this email message has been swept
for the
> presence of computer viruses, however we cannot guarantee that this
message
> is free from such problems.
>
************************************************** ********************************
>
>
> sending to informix-list