--- In firebird-support@yahoogroups.com, "volhoop" <mhoop81@...> wrote:

>
> My question is a simple one. Is there a performance difference between
> having a Primary key and INDEX?
>

They are different concepts. A primary key is a field (or set of
fields) that on a relational level you define to identify a record in
a table. This means that a primary key must be unique.

The Firebird implementation of the primary key is to create an
implicit ascending index to help it check this unique constraint. A
nice side effect of the supporting index is that it can assist when
the primary key field(s) are referenced inside where, join, or order
by clauses. I don't imagine this to change anytime soon because it is
the most efficient way I can think of to police the PK uniqueness and
a lot of people rely on PK indices in their queries.

A normal index is not unique unless it is declared to be. It may also
be ordered in a descending fashion which really assists queries that
order on the indexed field in a descending manner, or queries which
are interested in the max value.

A Firebird specific rule is that due to a bug(?) in the optimiser, you
should not declare both a constraint such as a PK or FK and an
ascending index on the same field. In some cases (or at least in the
past, it may be fixed now), the optimiser can't decide which index to
use and then does the least logical thing and decides it wont use either.

There may be some merit in adding a descending index to the PK
field(s) if you are constantly selecting the max or if you are
constantly ordering in a descending manner. (But do not use a select
max(PK) + 1 to generate a PK, it is a big no no and will get you into
all sorts of trouble if your system has simultaneous transactions).

A direct answer, there will be no measurable difference between the
two. They are both simply records in RDB$INDICES. Any difference in
performance will be attributable to other external factors, especially
caching.

Adam