What does the KEY keyword mean?

Question

In this MySQL table definition:

CREATE TABLE groups (
  ug_main_grp_id smallint NOT NULL default '0',
  ug_uid smallint  default NULL,
  ug_grp_id smallint  default NULL,
  KEY (ug_main_grp_id)
);

What does the KEY keyword mean? It's not a primary key, it's not a foreign key, so is it just an index? If so, what is so special about this type of index created with KEY?

It appears as though the "KEY" operator is no longer in use in MySQL Server 5.5. Not sure when it was removed, but an issue I'm having with it is present on 5.1, but not 5.5. — slant, Nov 05 '12 at 23:03
See also https://stackoverflow.com/questions/1401572/whats-the-difference-between-using-index-vs-key-in-mysql — Yves M., Sep 04 '19 at 15:19

score 225 · Accepted Answer · edited Sep 15 '21 at 15:42

225

Quoting from create-table - indexes andkeys

{INDEX|KEY}

So KEY is usually an INDEX

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.

edited Sep 15 '21 at 15:42

surfmuggle

5,527
7
48
77

answered May 29 '09 at 03:34

MartinodF

8,157
2
32
28

13

Notation {INDEX|KEY} in general does not mean that INDEX and KEY have the same meaning, or KEY is an INDEX ;) – sergtk Jan 04 '14 at 00:45
1

@sergtk: As per the notation in the MySQL manual, FOO|BAR means that either the keyword FOO or the keyword BAR can be used. Id est, they are synonyms. – dotancohen Jan 06 '14 at 17:33
5

Exactly! MySql manual clarifies that INDEX and KEY are synonyms - this is why I upvoted the answer. But it can be concluded from the answer that BNF Notation {|} described synonyms always, not only in this case. – sergtk Jan 06 '14 at 21:28
What if there is both "primary key" and key ? like `PRIMARY KEY (id), KEY id (id)` ? – Stphane Sep 09 '14 at 12:38
@dotancohen: Not at all. AND can be used wherever OR can be used and vice versa; that does not make them synonyms. – reinierpost Jan 27 '16 at 14:14
@reinierpost: Where in the fine manual do you see the notation `{AND|OR}` being used for the AND and OR keywords? – dotancohen Jan 27 '16 at 16:33
5

This is not correct. I found the best answer in Quora (see user Kristian Kohntopp) https://www.quora.com/What-is-the-difference-between-using-KEY-and-INDEX-in-MySQL: KEY is a constraint, INDEX is the data structure necessary to implement that constraint. In practice, if you have a FK for example `KEY key_name (user_id), CONSTRAINT foreign_key_constraint_name FOREIGN KEY (user_id) REFERENCES auth_user (id)` then you might additionally want to specify what INDEX is used (HASH vs BTREE). This example shows KEY and INDEX aren't synonyms. – Jan 31 '17 at 12:19
@oneloop: thank you for the link https://www.quora.com/What-is-the-difference-between-using-KEY-and-INDEX-in-MySQL . From there I quote the explanation made by Mr. Kristian Köhntopp, former Principal Consultant at MySQL (2005-2008). That is the sharpest explanation that I have found. In the page, his answer has the date Jul 2, 2015 – Robert Jul 12 '17 at 05:08
On this page: https://dev.mysql.com/doc/refman/8.0/en/create-table.html the create definition shows {FULLTEXT|SPATIAL} Later in the page these are described as being different types of indexes, therefore the syntax {a|b} does not mean that a and b are synonyms. This page: https://dev.mysql.com/doc/refman/8.0/en/manual-conventions.html states that it simply means that one member from the set of choices must be chosen. Nonetheless, this answer is right for the wrong reasons, as explained here: https://stackoverflow.com/a/1401615 – Jon Oct 11 '18 at 19:18
9

Nobody ever explained what's the use of that keyword, if it's obsolete or not. If I have a primary key, then why have a "key" too? what is the point of two indexes? plz enlighten me I can't find anything about this – Barbz_YHOOL Jun 28 '19 at 18:52
@Barbz_YHOOL the primary key is an index on your main column, but you may want to add indexes on other columns that are frequently queried. For example, the table `customers` may have a primary key `id` but also an index on `name` because you frequently have to look customers up by name. – bfontaine Jun 23 '22 at 17:55

score 38 · Answer 2 · edited Sep 04 '19 at 15:19

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.

column_definition:
      data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      ...

Ref: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

What does the KEY keyword mean?

2 Answers2

Linked

Related