Re: Primary Keys and Foreign Keys

Richard MacDonald wrote:
> Jan Hidders <hidders_at_REMOVE.THIS.win.tue.nl> wrote in message
> news:903d4e$5db$1_at_news.tue.nl...
>
> Followup to the followup: I've looked pretty hard but have been
> unable to find clear guidance for indexing on many-many tables. This
> actually applies more generally to composite indexes:
>
> Say I have a many-many table with the primary/candidate key being a
> composite of the two foreign keys. The database is going to create an
> index on these two fields.

It could, but it is more likely that it is going to define an index for the fields in the destination table.

> Now say that standard select queries will
> be one field or the other. Do I need to define two additional indices
> -- one for each of the two foreign keys?
>
> In other words, does a database index on a composite key
> provide assistance to select statements on a subset of these
> keys?

That depends upon how the index is implemented. If it is based on some type of lexicographical ordering of the two fields (B-trees et cetera) then it can help for looking up tuples by the "most significant" fields. But if it is based on something like hashing then it doesn't help at all. So I guess that in general the answer to your question is: no, it does not.

-- 
  Jan Hidders