RE: Index question
> -----Original Message-----
> From: owner-informix-list@iiug.org [SMTP:owner-informix-list@iiug.org]
> On Behalf Of Dirk Moolman
> Sent: Tuesday, May 17, 2005 10:17 AM
> 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)
The order that you use in the where is not supposed to affect
the optimizer. The optimizer will create a query plan based on
statistics, data distributions and the indexes which exist on the table.
> And create indexes on c & d (which brings up another question -
> composite or stand alone ?)
Composite and use the column with the best selectivity as the
lead column. You need to create data distributions with update
statistics and then look at them with dbschema to decide which column
has the best selectivity.
Regards,
Bill
> sending to informix-list
sending to informix-list