Re: Indexes & Primary Keys (based on the same columns)

In response to "Joshua D. Drake" <jd(at)commandprompt(dot)com>:

> Ow Mun Heng wrote:
> > I'm wondering if what I'm doing is redundant.
> >
> > I have a primary key on columns (A,B,C,D)
> > and I've also defined an index based on the same columns (A,B,C,D)
> >
> > and sometimes in the query explain, I see the pkey being used for the
> > scan instead of the index.
> >
> > So.. That made me think perhaps the additional index on the _same_
> > parameter is redundant.
>
> A primary key creates an index so having a second index with the same
> definition is redundant.

Note the "same definition."

Since this is a multi-column index, there may be some advantage gained
by having indexes defined slightly differently. I.e., your PK is
(ABCD) but you have an additional index on (DCBA)

Whether or not this is actually helpful depends on the nature of the
queries you run.

--
Bill Moran
http://www.potentialtech.com