Re: Indexing strategy

Richard Spitz suggested I share this. I was under the impression that Informix could use either part of the composite key, perhaps not as well as a dedicated key - but at least use it. Richard called me on that: > I am not quite sure if you are correct. Other replies stated that in > a composite index, the first row composing the index can be used for > queries on that row, but not the others. I think I've read about > that before. I just tried it. Table size 31,000+ rows fielda and fieldb with no indices: unload to file select * from table where fieldb = 'xxx' A) Without indices: avg 6.4s B) Add single index on fieldb: avg .28s C) Drop index and add composite index on fielda, fieldb: avg 6.28s D) Drop index and add composite index on fieldb, fielda: avg .28s sqlexplain.out says: A) - Sequential Scan B) - Index path fieldb C) - Sequential Scan ^^^^^^^^^^^^^^^^^^^^ D) - Index path fieldb It does use part one of the composite key if it can (D), but not part two (C). You are absolutely correct, I was wrong. Thank you I learned something. j. _____________________________________________________________________________ Jack Parker - Contractor | Hewlett Packard, BSMC Boise, Idaho, USA| If you keep staring at it like that, jparker@hpbs2561.boi.hp.com | your nose is going to grow (208) 396-5388 (W) (208) 384-1623 (H) | into the bark. _____________________________________________________________________________