A quick note on difference of key and index in MySQL

KEY

It has two functions: one is constrain in database(structure integrity or something); the other is to index(for accelerating querying)

​ primary key ​ unique key ​ foreign key The key is indexed because of the MySQL implementation:

"MySQL requires every Key also be indexed, that's an implementation detail specific to MySQL to improve performance."

INDEX

For index, it’s only for querying. When you define an index in table, MySQL will store in the table space as a format of “content”.

We have prefix index, full-text index, etc. But they will not constrains the behaviors of the field they are indexing.

However,

KEY is normally a synonym for INDEX. 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.

So these three queries have same function:

CREATE TABLE orders1 (
  order_id int PRIMARY KEY
);

CREATE TABLE orders2 (
  order_id int KEY
);

CREATE TABLE orders3 (
  order_id int NOT NULL,
  PRIMARY KEY ( order_id )
);

...while these 2 statements below (for orders4, orders5) are equivalent with each other, but not with the 3 statements above, as here KEY and INDEX are synonyms for INDEX, not a PRIMARY KEY:

CREATE TABLE orders4 (
  order_id int NOT NULL,
  KEY ( order_id )
);

CREATE TABLE orders5 (
  order_id int NOT NULL,
  INDEX ( order_id )
);

Enjoy.