Subject RE: [firebird-support] optimization question (select)
Author Leyne, Sean
> 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?