230

I know how to use INDEX as in the following code. And I know how to use foreign key and primary key.

CREATE TABLE tasks ( 
  task_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  parent_id INT UNSIGNED NOT NULL DEFAULT 0, 
  task VARCHAR(100) NOT NULL, 
  date_added TIMESTAMP NOT NULL, 
  date_completed TIMESTAMP, 
  PRIMARY KEY (task_id), 
  INDEX parent (parent_id), 
  ....


However I found a code using KEY instead of INDEX as following.

...
KEY order_date (order_date) 
...


I could not find any explanation on the official MySQL page. Could anyone tell me what is the differences between KEY and INDEX?

The only difference I see is that when I use KEY ..., I need to repeat the word, e.g.
KEY order_date (order_date).

informatik01
  • 15,636
  • 10
  • 72
  • 102
shin
  • 30,521
  • 65
  • 174
  • 258

5 Answers5

316

There's no difference. They are synonyms.

From the CREATE TABLE manual entry:

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.

akinuri
  • 9,179
  • 10
  • 57
  • 92
nos
  • 215,098
  • 54
  • 400
  • 488
  • 1
    When looking at table info in the latest version of SQLYog it shows `KEY keyname (column_name)` when I created the table with `INDEX keyname (column_name)`. The fact that it is a synonym explains it perfectly. – crmpicco Apr 02 '14 at 10:10
  • 2
    "The key attribute `PRIMARY KEY` can also be specified as just `KEY` when given in a column definition." What does this mean? – Zyl Oct 05 '20 at 09:13
14

Here is a nice description about the "difference":

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

informatik01
  • 15,636
  • 10
  • 72
  • 102
Stefan
  • 7,735
  • 3
  • 43
  • 96
  • 4
    Question is about the use of SQL Identifiers KEY and INDEX in MySQL. Not the difference between a key and an index. – Josh J Feb 02 '17 at 21:44
  • 1
    I searched for the difference with a search engine and this was the first result. In my opinion there is no need to open an extra question. But feel free to do so if you want to. – Stefan Mar 25 '20 at 16:00
7

It is mentioned as a synonym for INDEX in the 'create table' docs: MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.17 CREATE TABLE Syntax

@Nos already cited the section and linked the help for 5.1.

Like PRIMARY KEY creates a primary key and an index for you, KEY creates an index only.

Anis LOUNIS
  • 4,472
  • 5
  • 30
  • 58
fineliner
  • 144
  • 2
  • 4
6

Keys are special fields that play very specific roles within a table, and the type of key determines its purpose within the table.

An index is a structure that RDBMS(database management system) provides to improve data processing. An index has nothing to do with a logical database structure.

SO...

Keys are logical structures you use to identify records within a table and indexes are physical structures you use to optimize data processing.

Source: Database Design for Mere Mortals

Author: Michael Hernandez

JayD
  • 5,857
  • 4
  • 18
  • 23
  • 5
    Question is about the use of SQL Identifiers KEY and INDEX in MySQL. Not the difference between a key and an index. – Josh J Feb 02 '17 at 21:43
-1

A key is a set of columns or expressions on which we build an index.

  1. While an index is a structure that is stored in database, keys are strictly a logical concept.

  2. Index help us in fast accessing a record, whereas keys just identify the records uniquely.

  3. Every table will necessarily have a key, but having an index is not mandatory.

Check on https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721

Atulya Anand
  • 41
  • 1
  • 3
    Question is about the use of SQL Identifiers KEY and INDEX in MySQL. Not the difference between a key and an index. – Josh J Feb 02 '17 at 21:42
  • 1
    @Josh J Even if the original motivation was to ask about identifiers, searching for the difference about a key and an index with a search engine yields this topic. It could make sense to improve the title of the question to be more specific. On the other hand, writing a question about the difference between a key and an index in mysql would be possibly marked as a duplicate. =>I find such complementary answers very useful. The "real" answer will get the highest score, anyway. So I do not see a reason for negative votes on complementary answers. – Stefan Apr 08 '18 at 06:00
  • I have capitalized INDEX and KEY in the question to make this clear. – reinierpost Oct 17 '18 at 13:16