Is KEY and INDEX the same thing?

[Phil Latio]

I am following a book on PHP and MySQL and have come across the below SQL
statement.

CREATE TABLE users (
user_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(40) NULL,
password VARCHAR(16) NOT NULL,
registration_DATE DATETIME NOT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY username (username),
KEY first_name (first_name),
KEY last_name (last_name),
KEY password (password),
);

The author seems to switch from using INDEX to KEY in different chapters
without documenting why but they appear to do the same thing.

Could someone set me straight on this.

Cheers

Phil

[Markus Popp]

Yes, they are the same.

Markus

[Bill Karwin]

Phil Latio wrote:

I am following a book on PHP and MySQL and have come across the below SQL
statement.

CREATE TABLE users ( ....
PRIMARY KEY (user_id),
UNIQUE KEY username (username),
KEY first_name (first_name),
KEY last_name (last_name),
KEY password (password),
);

The author seems to switch from using INDEX to KEY in different chapters
without documenting why but they appear to do the same thing.

The syntax is a bit inconsistent, in my opinion, but in some
circumstances, the INDEX and KEY keywords can be used interchangeably.
In other circumstances, either INDEX or KEY is the only word that works.

For example:

legal: PRIMARY KEY
not legal: PRIMARY INDEX

legal: FOREIGN KEY
not legal: FOREIGN INDEX

legal: UNIQUE KEY
legal: UNIQUE INDEX

legal: CREATE INDEX ...
not legal: CREATE KEY ...

Anyway, it's a bit confusing. Refer to the reference documentation if
in doubt about the syntax:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Also, strictly speaking, "key" is a logical concept in relational
databases; it's a column or set of columns that identifies a record.
"Index" is a physical entity in a database that is frequently used to
improve performance, enforce uniqueness efficiently, perform quick
lookups, etc. It is not logically related to the concept of a key, but
they are often used together. An RDBMS could support keys without using
indexes, but it would work very slowly. For that reason, in many RDBMS
implementations, declaring a column as a key implicitly creates an index.

Regards,
Bill K.

[Phil Latio]

> The syntax is a bit inconsistent, in my opinion, but in some

circumstances, the INDEX and KEY keywords can be used interchangeably.
In other circumstances, either INDEX or KEY is the only word that works.

For example:

legal: PRIMARY KEY
not legal: PRIMARY INDEX

legal: FOREIGN KEY
not legal: FOREIGN INDEX

legal: UNIQUE KEY
legal: UNIQUE INDEX

legal: CREATE INDEX ...
not legal: CREATE KEY ...

Anyway, it's a bit confusing. Refer to the reference documentation if
in doubt about the syntax:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Also, strictly speaking, "key" is a logical concept in relational
databases; it's a column or set of columns that identifies a record.
"Index" is a physical entity in a database that is frequently used to
improve performance, enforce uniqueness efficiently, perform quick
lookups, etc. It is not logically related to the concept of a key, but
they are often used together. An RDBMS could support keys without using
indexes, but it would work very slowly. For that reason, in many RDBMS
implementations, declaring a column as a key implicitly creates an index.

Regards,
Bill K.

Thanks very much for your excellent explanation (and to Markus aswell).

I have been searching the web this afternoon for articles relating KEY or
INDEX and nothing compares to above.

Cheers

Phil