Re: Indexing

Duc writes: | Could someone tell me how Informix pick and choose which index to use | if I have a table with 2 indexes as following: | table t_a ( | f_1 integer, | f_2 integer, | f_3 integer, | ..... | ) | | index i_1(f_1, f_2) | index i_2(f_3) | | and the where clause is something like this: where f_1 = 10 and f_3 = 15. As far as I know, It will first search for a composite index on both f_1 and f_3: Failing that, It will probably search for an index with one of f_1 or f_3 being the leading column in the index (or both) - in this case It will find the f_1 index, use this one to search for 'f_1 = 10', and apply a lower index filter to each row found to match 'f_3 = 15'. (It being the Optimazer) If there are no suitable indexes, then it will use information like B-tree depth, number of rows in each table, distribution of column values to decide which column (s) to do a sequential search on. This can, however, depend on which version of the optimizer you are running, which version of update statistics you ran last, etc ... etc... If you can get hold of the Informix SQL Tutorial version 4.1 (I think), this (or something similar) has a good description on the optimidator, or queries at least, and how it should work Hope this helps, Richard Ridley DHL Asia Pacific