All things being equal IN and NOT IN will be equally slow.

-> -----Original Message-----
-> From: tomcaml@yahoo.com [mailto:tomcaml@yahoo.com]
-> Sent: Thursday, March 03, 2005 4:03 PM
-> To: informix-list@iiug.org
-> Subject: 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
->

sending to informix-list