Re: Difference between PRIMARY KEY index and UNIQUE-NOT NULL index

On 7/20/07, Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:
>
> On Jul 20, 2007, at 17:54 , Vincenzo Romano wrote:
>
> > In an inner join involving a 16M+ rows table and a 100+ rows table
> > performances got drastically improved by 100+ times by replacing a
> > UNIQUE-NOT NULL index with a PRIMARY KEY on the very same columns in
> > the very same order. The query has not been modified.
>
> There should be no difference in query performance, AIUI.

If I read the documentation correctly, PRIMARY KEY is simply syntactic
sugar equivalent to UNIQUE + NOT NULL, the only difference being that
a PRIMARY KEY is reported as such to someone looking at the table
structure, which becomes more intuitive than seeing UNIQUE + NOT NULL.

>
> > In the older case, thanks to the EXPLAIN command, I saw that the join
> > was causing a sort on the index elements, while the primary key was
> > not.
>

Might it just be that the original UNIQUE + NOT NULL index was bloated
or otherwise degraded, and reindexing it would have resulted in the
same performance gain? That's just a guess.

-Josh