Thread: implicit index created, do I need to also create an explicit index?

So I create a table with unique fields, and it creates an implicit index.

1.  This is a general purpose index, correct?  I.e. not used solely for
     ensuring uniqueness, so I don't need to create another index on
     the same field explicitly, right?

2.  What are the implications of creating another index, such as
      create index foo on tv_workspace(name)?  Is is just a duplicate
     of the implicitly created index?


create table tv_workspace (
     name varchar(32) unique not null,
     p4client varchar(64) unique not null,
     owner bigint not null
);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tv_workspace_name_key" for table "tv_workspace"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tv_workspace_p4client_key" for table "tv_workspace"


TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

On Fri, Nov 18, 2005 at 01:38:59PM -0800, Mark Harrison wrote:
> So I create a table with unique fields, and it creates an implicit index.
>
> 1.  This is a general purpose index, correct?  I.e. not used solely for
>     ensuring uniqueness, so I don't need to create another index on
>     the same field explicitly, right?

Right

> 2.  What are the implications of creating another index, such as
>      create index foo on tv_workspace(name)?  Is is just a duplicate
>     of the implicitly created index?

Well, if one is unique and the other isn't, they're not quite
identical. But the latter is redundant and probably won't ever be
used...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.