Re: Indexing problem

> I have a table in Informix Online Dynamic Server 7.2 with 6 columns and > 10,000 rows. I created a composite unique index on two columns ( one is > serial with not null and the other integer with not null) and dupls index > on integer column. In a select query Iam comparing serial column with > program variable value and joining with other table using integer column > in the where clause. Query retreival time is 75 seconds . Then I created > an index on serial column . Query retreival time is 2 seconds. My question > is why it is faster in the second case and why can't the composite index > is enough. I suspect that your statistics had not been updated recently, which may have caused either an index scan or a tablespace scan, or Informix may have even started the scan with the other table. One thing for sure, though, is that the unique composite index was not sufficient. It merely guaranteed that the COMBINATION of the serial key and the integer would be unique, rather than either or both of the individual columns. Once you created the unique index on the serial key, Informix knew that there would be at most one row with the value you specified. This caused it to change its access path and perform an index lookup on the serial key, read the data row to get the integer, then perform the join. Try running the query with SET EXPLAIN ON both with and without the serial key index and compare the generated access paths. Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.