Subject Re: Index Coverage in Firebird
Author Adam
> We are trying to use the Index Coverage concept (as available in other
> databases) in Firebird. But we are not getting any performance benefits
> with this.
> We are trying to fetch records from a 24 Million record table, and in
> order to avoid the fetch from both index and the actual table, we
> included the required non-key field in the index so that the Firebird
> database does not read the table but fetches data from the index.
> However, there is no improvement in the time taken in both approaches.

Indices in firebird are treated differently than many other databases.
Data is stored in an arbitrary position, not ordered by PK. In
Firebird, the index is a lot more dense than these other databases,
meaning that it is less expensive to fetch from the index, and
multiple indices can be combined to solve a condition.

The truth is, that if the index did store enough information to answer
the query, it would be much larger and slower to read, so it is a bit
of 'robbing Peter to pay Paul'.

This page will help you see the difference:

How many records match your query criteria?
What is the table structure?
What is the query?
What index or indices do you have to help the query?
What is the query plan - you can check the query plan using any admin
tool or iSQL (type 'set planonly;', then run your query).