thanks ann for your rich and precious help !

i try to put an index on fieldA and another on fieldB but unfortunatly it's does not help (in my situation and my particular query params). it's really depend of the parameter (if the where it not restrictive on fieldA and too much restrictive on fieldB or the opposite, then using an index or the other could be very different in speed... but it seam that using both not really help)

my probleme, is on table with huge amount of row (> 8 000 000) the speed is a requierement and it's forbidden to do a natural table scan. i must find a way to firebird to get only the intersection of filter

> > select * from BIGTABLE
> > where
> > fieldA > xxx and
> > fieldA < yyy and
> > fieldB > www and
> > fieldB < ppp

actually i do

PLAN (BIGTABLE INDEX (FIELDA_IDX, FIELDB_IDX))

but it's not help :(

it's depend of course of the params in xxx and yyyy and www and ppp but did it's mean that i will need to manually look if the select are restrictive or not and manually choose the appriopriate index to use ?

isn't any other way? denormalzing the database for exemple ? i must be able to have the result of all query in few ms

thanks for all ann
stephane

--- In firebird-support@yahoogroups.com, "Ann W. Harrison" <aharrison@...> wrote:
>
> Leyne, Sean wrote:
> > Dimitry,
> >
> >>> 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.
> >
> > You don't have enough information to make such a broad statement!
> >
> > It is possible that indexing either column is the wrong answer, and that a natural table scan would be the faster approach.
> >
> > But to know the correct answer we need to ask questions, and not jump to conclusions.
>
> On the other hand, this is a good time to explain to those who are new
> to Firebird that its index access strategy allows it to use two or more
> indexes on a single query. When Firebird optimizes a query it finds all
> possible indexes that could be used, and selects the primary access
> strategy that will, in theory, minimize the number of rows read. That's
> what all optimizers do. Then Firebird takes a second pass at the access
> plan and adds in additional indexes that may further refine the result
> sets.
>
> During query execution, Firebird reads the index for the primary access,
> setting bits in a sparse bitmap to indicate which record numbers meet
> the indexed criteria. It than reads each other index that refines
> access to that table, again setting bits in a sparse bitmap. Then it
> combines the bitmaps to find only the records that met all criteria.
>
> So, in the query
>
> >
> > select * from BIGTABLE
> > where
> > fieldA > xxx and
> > fieldA < yyy and
> > fieldB > www and
> > fieldB < ppp
>
> Firebird can use an index on fieldA and an index on fieldB and find
> only the intersection of records that meet the fieldA and fieldB
> criteria.
>
> In this case, it can't get the same refinement from a single index
> on fieldA and fieldB. There's no logic in the execution engine
> for reading some entries and setting bits for qualifying rows then
> skipping some entries that don't qualify then continuing with the
> next qualifying entries. So an index on fieldA, fieldB works no
> better than an index on just fieldA, while two indexes, one on each
> field, does (generally) improve selectivity.
>
> As Sean said, it all depends. If every row in fieldB is between
> www and ppp, then there's no gain.
>
>
> Good luck,
>
> Ann
>