> it's my optimization day today. thanks to dinitry i understand how to
> otpimize my order by.
>
> now how to optimize this simple SQL :
>
> select * from BIGTABLE
> where
> fieldA > xxx and
> fieldA < yyy and
> fieldB > www and
> fieldB < ppp
>
> fieldA and fieldB are numeric(15,5)
>
> create a index on (fieldA, fieldB) seam to no help too much (it's speed
> only the work on FieldA, but not on fieldB)
>
> in the worse case (xxx and yyy too large and www and ppp too
> restrictive) this simple query with an plan using an index on (fieldA,
> fieldB) can take more than 10 seconds to return :(

Depends on the answers to:

1 - How many rows in the table

2 - What is the total length of each row in the table?

3 - Which column (fieldA or fieldB) has/will have the most unique values? Is there a even distribution of values amongst the rows?

4 - Will one column be more narrowly queried compared to the other, on a consistent basis?


Sean