On 7/20/07, Michael Glaesemann <grzm@xxxxxxxxxxxxxxx> 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
