Thanks a lot dimitry !

unfortunatly put an index on fieldA and another on fieldB not help ! same speed (or i miss something, i probably write badly the query plan ?)

PLAN (BIGTABLE INDEX (FieldA_IDX, FieldB_IDX))

to gave you an idea, the bigtable have
8 000 000 records
fieldA = Price (between 0 to 1000000, mostly around 250000)
fieldb = Size (between 0 to 200 mostly around 60)

so when i do

where
fieldA > 1 and
fieldA < 10000000 and
fieldB > 5 and
fieldB < 6

then using the index on fieldA not help at all because the filter is not really restrictive on fieldA when in the oposite the filter on fieldB is too much restrictive. in the previous case i need to use only the index on fieldB. but in the same way what if the user do

where
fieldA > 1 and
fieldA < 2 and
fieldB > 1 and
fieldB < 1000

i m in the same trouble as previous (just in another side) ! and the optimizer don't know with index to choose (myself too i don't know as every situation or parameter could be different)

just want to know how the developper face such "trivial" problem on big table (where the query could return in more than 20 seconds in the worse case, when normally it's must return in few ms)

again thanks for you precious help dmitry !
stephane

--- In firebird-support@yahoogroups.com, Dimitry Sibiryakov <sd@...> wrote:
>
> > create a index on (fieldA, fieldB) seam to no help too much (it's speed only the work on FieldA, but not on fieldB)
>
> Drop this useless composite index and create two indexes one single
> fields. i.e. index on fieldA and another index on fieldB.
>
> SY, SD.
>