Re: indexes
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