RE: Index question

Thanks everyone - very interesting one, and the feedback was all very similar. We ended up creating a composite index on (c,d,a,b) - which brought the process down from 7 minutes down to 20 seconds :-) -----Original Message----- From: Obnoxio The Clown [mailto:obnoxio@serendipita.com] Sent: 17 May 2005 08:35 PM To: Dirk Moolman Cc: informix-list@iiug.org Subject: Re: Index question Dirk Moolman said: > 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