Indexes

hello, here is my question. if you have a table called related_to and it has these fields: id active from_class_id from_id type_cid status_cid seq_num to_class_id to_id created_by create_date updated_by, update_date with this index: create index "informix".related_to_i2 on "informix".related_to (to_id,to_class_id,active,type_cid,seq_num,from_class_id,from_id); and you run this sql against it: SELECT MAX(seq_num) FROM related_to WHERE type_cid = 3030 AND from_class_id = 74 AND from_id = 2184640 AND ACTIVE = 1 does it matter the order of the created index and the order of the fields in the sql? on a more general note, how do informix indexes work? do they have to be in the same order? what if there is a mismatch between all the columns in the index and what is in the sql? Thanks in advance, Tom

tomL wrote: > hello, > here is my question. if you have a table called related_to > and it has these fields: > id > active > from_class_id > from_id > type_cid > status_cid > seq_num > to_class_id > to_id > created_by > create_date > updated_by, > update_date > > with this index: > create index "informix".related_to_i2 on "informix".related_to > (to_id,to_class_id,active,type_cid,seq_num,from_class_id,from_id); > > and you run this sql against it: > SELECT MAX(seq_num) > FROM related_to > WHERE type_cid = 3030 AND from_class_id = 74 AND from_id = 2184640 > AND ACTIVE = 1 > > does it matter the order of the created index and the order of the > fields in the sql? > > on a more general note, how do informix indexes work? > do they have to be in the same order? > what if there is a mismatch between all the columns in the index and > what is in the sql? > > Thanks in advance, > Tom Indexes in Informix work much like indexes in any other DB. In general for an index to be choosen by the optimizer to solve a query there must be a condition (in the query) referencing the first index column. In the above case, there is no condition on to_id in the query. As such it will make a sequential scan. You can verify this by using the SQL instruction "SET EXPLAIN ON;" and looking at the "sqexplain.out" file in the $HOME of the user. You'll see the query plan. Don't forget to update statistics after creating indexes. Some versions of Oracle (9i I think) can eventually use an index when there are conditions on the second (and maybe others) columns of the indexes. Take note that I mention "eventually" because ther must be some pre-conditions for this to be true: 1- The first column of the index must have very low selectivity (in which case you'd better think why it was chosen for index header) 2- The table must have very good statistics collected and maybe others I don't remember. Please note that this is an exceptional situation. The normal index behavior is the mentioned first. Think about the physical structure and layout of an index and you'll understand why. Regards.