RE: indexes

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