Index question

Posted in 2005

Dirk Moolman — Tue, 17 May 2005 16:17:08 +0200 — source: Usenet: comp.databases.informix

We have a new query, with a where-clause: where a < b (a & b are date fields) and c = 0 (c can have values from 0 to 270) and d = "2" (d can have 4 different values) What is the best way to write this where clause, and which are the best indexes to use ? The table has a total of 32 million records. My own thoughts were: where d = "2" (get rid of most of the records) and c = 0 (reduce the above subset to about 25%) and a < b (leave the sequential scan for this remaining data set) And create indexes on c & d (which brings up another question - composite or stand alone ?) sending to informix-list

↪ replying to Dirk Moolman

Art S. Kagel — Tue, 17 May 2005 18:38:33 -0400 — source: Usenet: comp.databases.informix

Dirk Moolman wrote: It does not matter. The IDS optimizer uses costs to determine the best query plan. The order of the filters and join conditions in the WHERE and ON clauses do not affect the choices that the optimizer makes. You CAN affect the optimizer's decisions my maintaining sufficiently detailed statistics in the database by running UPDATE STATISTICS according to the recommendations in the Performance Guide (or as implemented in my dostats utility) and by providing appropriate indexes. To that know that IDS will only use ONE index per table so if you create singleton indexes on a, c, & d they will not be combined to filter this query, only the one providing the best filter value will be used. A composite index containing all three will help greatly though. Art S. Kagel > We have a new query, with a where-clause: > > where a < b (a & b are date fields) > and c = 0 (c can have values from 0 to 270) > and d = "2" (d can have 4 different values) > > > What is the best way to write this where clause, and which are the best > indexes to use ? > > The table has a total of 32 million records. > > > > > My own thoughts were: > > where d = "2" (get rid of most of the records) > and c = 0 (reduce the above subset to about 25%) > and a < b (leave the sequential scan for this remaining data > set) > > > And create indexes on c & d (which brings up another question - > composite or stand alone ?) > > > > sending to informix-list