Re: indexing primary and foreign keys w/lookup table

On Wed, Jan 24, 2007 at 20:14:07 -0800,
Neal Clark <nclark(at)securescience(dot)net> wrote:
> I was wondering...I currently have indexes on the primary key id and
> foreign key id's for tables that resemble the following. Is this a
> good idea/when would it benefit me? I don't want waste a lot of
> unnecessary space on indexes.

Not exactly. Primary keys already result in an index being created to enforce
uniqueness, so the manually created indexes are redundant.

> CREATE TABLE stuff_by_account (
> account_id BIGINT REFERENCES accounts(id),
> stuff_id BIGINT REFERENCES stuff(id)
> );
> CREATE INDEX stuff_by_account_account_id ON stuff_by_account
> (account_id);
> CREATE INDEX stuff_by_account_stuff_id ON stuff_by_account(stuff_id);

For this last case, you most likely want to declare either account_id, stuff_id
or stuff_id, account_id as a primary key. You may want to create an index
just on the second column of the primary key, depending on your usage pattern.
You almost certainly wouldn't want to create an index on the first column
of the primary key.