composite index question

hi all: If I put a composite index on three fields, a,b,c, what kind of query would this benefit? I made a few queries with differenct criterias, and set explain on, this is what I discovered. 1. if the criteria is only on a or b or c, it is a sequential scan. 2. if the criteria on both a and b and c, it uses the index. 3. if the criteria on both a and b, it uses the index. 4. everything else is a sequential scan. So if I use a composite scan, pretty much the only thing benefits are queries which have all fields as creterias? Am I correct? I remember when I use Access, such index helps all queries with all combinations of a and b and c, is informix handling this differently? any help is greatly appreciated. thanks. yan

Yan Zhu wrote: > > hi all: > If I put a composite index on three fields, a,b,c, what kind of > query would this benefit? > > I made a few queries with differenct criterias, and set explain on, > this is what I discovered. > 1. if the criteria is only on a or b or c, it is a sequential scan. > 2. if the criteria on both a and b and c, it uses the index. > 3. if the criteria on both a and b, it uses the index. > 4. everything else is a sequential scan. > > So if I use a composite scan, pretty much the only thing benefits > are queries which have all fields as creterias? > Am I correct? I remember when I use Access, such index helps all queries > with all combinations of a and b and c, > is informix handling this differently? As someone once said to me in a different context. "In your small statistical sample that seems to be true". Actually the optimizer MAY use a compound index built on a then b then c for any query that includes a filter or join on column a if there is not a single column index on a alone. It will favor the compound index for queries that also include a filter or join on column b over an index on a alone. In addition the optimizer MAY use the compound index for any query that contains an ORDER BY or GROUP BY that specifies a leading subset of the columns of the index. Notice I said the optimizer MAY use the index. The optimizer is a pure cost based optimizer (ignoring optimizer directives and settings for now) and is free to use or not use the index depending on whether using the index will reduce costs (primarily I/Os). Whether the index is used in a particular query depends on the filter condition, the data distribution of the column(s) involved in the index/query, the efficiency of the particular index (key length, #levels, & #nodes) versus other useful indexes, etc. If the number of rows in the table is very small, or if the data distribution indicates that the cost of reading and processing the index pages would be unwarranted or wasted (because almost all pages need to be read anyway), the optimizer may choose to perform a sequential pr parallel scan instead. In addition, if the table is fragmented, especially round robin, a table scan may be determined to be the best way to select the rows. Things to look at: o Was the test table very small? Fragmented? o How fine was the filter condition (did it eliminate many rows)? o Have you updated statistics AT LEAST to medium, better HIGH on the first column of each index and on the first column that differs if multiple indexes start with the same column subset. (Get dostats.ec) o Have many rows been deleted (can lead to an inefficient index with too many levels if the deleted rows will not be replaced by others)? Art S. Kagel