primary key vs unique key

[Westcoast Sheri]

Which will be a faster lookup of an item by, "color" in the following
mySQL tables: "unique key," "primary key," or just plain "key"??

CREATE TABLE myTable (
number int(11) NOT NULL default '0',
description varchar(50) NOT NULL default '',
color varchar(30) NOT NULL default '',
price decimal(3,2) NOT NULL default '0.00',
UNIQUE KEY (color)
);

CREATE TABLE myTable (
number int(11) NOT NULL default '0',
description varchar(50) NOT NULL default '',
color varchar(30) NOT NULL default '',
price decimal(3,2) NOT NULL default '0.00',
KEY (color)
);

CREATE TABLE myTable (
number int(11) NOT NULL default '0',
description varchar(50) NOT NULL default '',
color varchar(30) NOT NULL default '',
price decimal(3,2) NOT NULL default '0.00',
PRIMARY KEY (color)
);

[Erwin Moller]

Re: primary key vs unique key

Westcoast Sheri wrote:
[color=blue]
> Which will be a faster lookup of an item by, "color" in the following
> mySQL tables: "unique key," "primary key," or just plain "key"??[/color]

Hi, this is not a PHP related question, is it?

I don't know the answer to your question, and don't know mySQL good, but in
general:
Once a column is indexed, the retrieval based on that column (color here)
will be fast.
If you put a contraint like UNIQUE KEY or PRIMARY KEY on it, it will be
indexed.
(I don't know what KEY means...)

The speed of retrieval of an indexed column is based on the number of
records (doooh) and the storagelogic. I *think/expect* mySQL uses B-tree to
create indexes.

The best way to answer your question is: test it.
Just fill that table with 10.000.000 records and do some timetesting.
(I expect that an UNIQUE or PRIMARY constraint give the same time.)

Sorry I cannot be of more help.
Good luck,

Regards,
Erwin Moller
[color=blue]
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> UNIQUE KEY (color)
> );
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> KEY (color)
> );
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> PRIMARY KEY (color)
> );[/color]

[Michael Austin]

Re: primary key vs unique key

Westcoast Sheri wrote:[color=blue]
> Which will be a faster lookup of an item by, "color" in the following
> mySQL tables: "unique key," "primary key," or just plain "key"??
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> UNIQUE KEY (color)
> );
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> KEY (color)
> );
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> PRIMARY KEY (color)
> );
>[/color]


Primary Key: column(s) that is/are used to reference data in other
tables - Parent-child relationship and there may be duplicates - see
docs for restrictions and usage

Unique Key: column or columns that make a record UNIQUE.
Firstname+Lastn ame is not necessarily unique, SSN is Unique. NO
Duplicates allowed - this is why it is called UNIQUE.

Foreign Key constraint is used to enforce data integrity such that this
key field must also exist in a "primary" table.

and with MySQL I have no clue what KEY is, I guess I will have to go
read the docs too...

from:

http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

KEY is normally a synonym for INDEX. From MySQL 4.1, the key attribute
PRIMARY KEY can also be specified as just KEY when given in a column
definition. This was implemented for compatibility with other database
systems.

In MySQL, a UNIQUE index is one in which all values in the index must be
distinct. An error occurs if you try to add a new row with a key that
matches an existing row. The exception to this is that if a column in
the index is allowed to contain NULL values, it can contain multiple
NULL values. This exception does not apply to BDB tables, for which
indexed columns allow only a single NULL.

Michael Austin.
OracleRdb and Oracle7/8i/9i DBA.

[Andy Hassall]

Re: primary key vs unique key

On Tue, 06 Jul 2004 18:08:59 GMT, Michael Austin <maustin@firstd basource.com>
wrote:
[color=blue]
>Primary Key: column(s) that is/are used to reference data in other
>tables - Parent-child relationship and there may be duplicates - see
>docs for restrictions and usage[/color]

Maybe I'm just reading your sentence incorrectly, but it sounds like you're
saying you can have duplicates in the primary key itself. You can't have
duplicates in a primary key.

--
Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

[Brad Kent]

Re: primary key vs unique key

Obviously if you put a unique restraint on the column you can't have
two "orange" or whatever.

Westcoast Sheri <sheri_deb88@no spamun8nospam.c om> wrote in message news:<40EA0557. F066BB2E@nospam un8nospam.com>. ..[color=blue]
> Which will be a faster lookup of an item by, "color" in the following
> mySQL tables: "unique key," "primary key," or just plain "key"??
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> UNIQUE KEY (color)
> );
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> KEY (color)
> );
>
> CREATE TABLE myTable (
> number int(11) NOT NULL default '0',
> description varchar(50) NOT NULL default '',
> color varchar(30) NOT NULL default '',
> price decimal(3,2) NOT NULL default '0.00',
> PRIMARY KEY (color)
> );[/color]

[Michael Austin]

Re: primary key vs unique key

Andy Hassall wrote:[color=blue]
> On Tue, 06 Jul 2004 18:08:59 GMT, Michael Austin <maustin@firstd basource.com>
> wrote:
>
>[color=green]
>>Primary Key: column(s) that is/are used to reference data in other
>>tables - Parent-child relationship and there may be duplicates - see
>>docs for restrictions and usage[/color]
>
>
> Maybe I'm just reading your sentence incorrectly, but it sounds like you're
> saying you can have duplicates in the primary key itself. You can't have
> duplicates in a primary key.
>
> --
> Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
> http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space[/color]


My mistake, a primary key constraint will only allow a unique entry,
even if there are multiple columns in the PK. I am not sure what I had
in mind when I wrote that... my brain went on vacation and has not yet
returned... now where did it go this time ???

Michael Austin.