Subject RE: [firebird-support] Composite Primary Keys and Indices
Author Helen Borrie
At 05:51 AM 22/07/2004 -0700, you wrote:
>Thanks Dimitry, but I'm a little confused or I may not completely
>understand a composite primary key or how indexing works. It *looks*
>like there is a single (implicit) composite ascending index created for
>the composite key.

No, it's not implicit at all. Firebird automatically creates an ascending
(by default) index on the key columns named in a PRIMARY KEY constraint.

>But when I query for COLUMN_OWNER_ID, I am only
>searching on a single column. Why doesn't creating another index on that
>single column improve performance?

Firebird can always use the leftmost column(s) of an index as though it
(they) were an index in their own right. Sometimes, it confuses the
optimizer to have two identical (or effectively identical) indexes to
choose from and it might decide on a less optimal access method instead of
an index. But, be assured, if COLUMN_OWNER_ID is the first key column in
your PK, the PK's index will be used by the optimizer if it is the fastest
way to do the search.

>FWIW, I've tried it and you are
>right, it does not improve performance; I just don't know why. If this
>is a pain to answer feel free to point me to some resource where I can
>learn about it myself, and thanks for the help.

It's explained in some detail in Chapter 18 of "The Firebird Book", a
black, red and yellow doorstopper coming to a bookstore near you in about a
week from now.