dear seam,

i just gave the data in the answer to dimitry ... is it ok or you need something else ?

thanks for your help !

stephane

--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...> wrote:
>
>
>
> > 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
>