Re: indexing

Thanks, Art. How about this scenario? What if the index contains fields I'm not specifying? will the index still be used? Tom "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.08.30.17.16.08.846438.1355@bloomberg.net>... > On Mon, 30 Aug 2004 14:33:39 -0400, tomL wrote: > > > hello all! > > > > a few indexing questions: > > > > If the columns I filter on in my query are a subset of the columns in an > > index, will the index be used? > > If the subset includes the first 'n' columns of an index that index MAY be > used depending on the optimizer's determination of the filter value of using > that index versus some other versus a sequential scan. That depends mostly > on the quality of the table's Data Distributions created with UPDATE > STATISTICS MEDIUM/HIGH. See the Informix Performance Guide for details. > > > Also -- does the order in which the columns are specified in my filter > > matter? > > In general, no, the optimizer reorders the comparisons it makes as needed to > minimize the cost of the query. > > Art S. Kagel > > > thank you > > tom

On Tue, 31 Aug 2004 09:48:37 -0400, tomL wrote: > Thanks, Art. > > How about this scenario? > What if the index contains fields I'm not specifying? will the index still > be used? OK, to be specific, if you have an index defined as: CREATE INDEX freddy_ix1 ON freddy( col1, col2, col3, col4 ); and a query that looks like this: SELECT * FROM freddy WHERE col1 = 12 AND col3 = 22; the engine MAY decide to use the index freddy_ix1 for a direct lookup on col1=12 and it will perform a low level filter on the returned keys that match col1=12 to see if the third keyfield matches, col1 = 22. But unlike the following query, the engine has to examine EVERY key that has col1=12. In the query: SELECT * FROM freddy WHERE col1=12 AND col2=1 AND col3=22; The engine again MAY use the index freddy_ix1 and if so will only have to examine the few keys that match all three of the specified columns' filter values. However, for this query: SELECT * FROM freddy WHERE col2=1 and col3=22 and col4=9; The engine will NEVER use the index freddy_ix1 because the lead column, col1, in not included in the filter specifications. Whether the index is ACTUALLY used will depend on other matters, including what other indexes are available (like freddy_ix2 below), what the level of UPDATE STATISTICS is, what the actual data distributions show, and if thequery is more complex then if using another index to improve join speed at the expense of filter speed might be better. Consider: CREATE INDEX freddy_ix2 ON freddy( col2, col3, col1, col4 ); CREATE INDEXfreddy_ix3 ON freddy( col1, col3, col2, col4 ); CREATE INDEX freddy_ix4 ON freddy( col3, col2, col1, col4 ); For the first query above the optimizer might elect to use ANY of the three indexes depending on the data distributions available and what they show. While using index freddy_ix3 would seem to be best, it may be that the data distribution shows only one row is likely to contain the value 22 for column col3 which would make the filter value of index freddy_ix4 far better for this particular filter, requiring the examination of several fewer index nodes, even though on the surface it would seem better to use freddy_ix3 and filter on both col1 and col3. Art S. Kagel > Tom > > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2004.08.30.17.16.08.846438.1355@bloomberg.net>... >> On Mon, 30 Aug 2004 14:33:39 -0400, tomL wrote: >> >> > hello all! >> > >> > a few indexing questions: >> > >> > If the columns I filter on in my query are a subset of the columns in an >> > index, will the index be used? >> >> If the subset includes the first 'n' columns of an index that index MAY be >> used depending on the optimizer's determination of the filter value of >> using that index versus some other versus a sequential scan. That depends >> mostly on the quality of the table's Data Distributions created with UPDATE >> STATISTICS MEDIUM/HIGH. See the Informix Performance Guide for details. >> >> > Also -- does the order in which the columns are specified in my filter >> > matter? >> >> In general, no, the optimizer reorders the comparisons it makes as needed >> to minimize the cost of the query. >> >> Art S. Kagel >> >> > thank you >> > tom