SEO

Dirk

It really depends on what other queries there are on this table.
If there are more queries using c and not d then create on c,d,
else if more on d than c then create on d,c if about equal then
create one on c,d and one on d alone. If this is the only query
then index c,d,a,b to get best performance as the entire selection
will be done within the index and only for those matching rows
will the data be retrieved.
Normally the best way to plan an index is to put the most unique
value first hence c before d, unless the values of c are heavily
weighted to a small range. and UPDATE STATISTICS

Keith

-> -----Original Message-----
-> From: Dirk Moolman [mailtoirkM@mxgroup.co.za]
-> Sent: Tuesday, May 17, 2005 3:17 PM
-> To: informix-list@iiug.org
-> Subject: Index question
->
->
-> 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
->

************************************************** ********************************
This message is sent in strict confidence for the addressee only. It may
contain legally privileged information. The contents are not to be disclosed
to anyone other than the addressee. Unauthorised recipients are requested
to preserve this confidentiality and to advise the sender immediately of any
error in transmission.
This footnote also confirms that this email message has been swept for the
presence of computer viruses, however we cannot guarantee that this message
is free from such problems.
************************************************** ********************************


sending to informix-list