At 07:23 PM 11/19/2004, David Johnson wrote:

>For better performance, you would typically put the more unique value
>first in the index, then the less unique.

Not in Firebird. Our indexes use prefix compression so duplicate
leading values aren't represented in the stored key. For best
compression, the keys should be ordered from least to most selective.
Best compression leads to the fastest lookup because it minimizes
the need to read index pages.

However, the original question was from someone relatively new to
databases, so let me back away from that statement a bit.

If you have three fields, country, province, and city, and want to
put a compound index on them, you should think only about how you
will use the index, not about how it will store data. You can use
a compound index when your comparison includes all fields in the
index, or the leading fields. If you have an index on fields A,B,C,D
in that order, the engine will use that index to resolve these
queries

select <blah> from <table>
where A = 1 and B = 2 and C = 3 and D = 4

select <blah> from <table>
where A = 1 and B = 2 and C = 3

select <blah> from <table>
where A = 1 and B = 2

select <blah> from <table>
where A = 1

The index can be used to resolve A in this query

select <blah> from <table>
where A = 1 and C = 3 and D = 4

The index can be used to resolve A and B in this query

select <blah> from <table>
where A = 1 and B = 2 and D = 4

The index can never be used if you don't supply a value
for A. Even if you supply values for C and D, the index
can only be used to resolve A unless you also supply a
value for B. It can be used only for A and B unless you
supply a value for C.

Firebird can use two (or more) indexes to resolve a single
query, so you're often (but not always) better off putting
separate indexes on A, B, C, and D if there are cases where
you can supply different combinations of those values.

The exception to that rule is that non-selective indexes
can be slow. So if one of your fields has only a few
distinct value (e.g. sex), then it's probably better as
part of a compound index that makes it more selective.

And that, really, is all you need to know for indexing 101.

David Johnson's questions are part of indexing 203 for those
already clear that the first rule is "know how the index will
be used and create an index that will support the queries you
anticipate."

>I haven't checked yet - does firebird support index only access paths?

No. Indexes in Firebird are multi-generational - the same
record will have more than one index entry if key values have
been modified. Therefor, you can't be sure that the value
in the index is appropriate for the current transaction - you
have to look at the record itself.



Regards,


Ann
mod