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