[MysQL] What is "KEY"?

[Chris]

What exactly is "KEY" (speaking strictly about MySQL performance/features)? Is it just a fancy name for INDEX?


When you use PRIMARY KEY, it adds INDEX as well. Is there some "KEY" that does not create INDEX?

Are UNIQUE KEY and UNIQUE INDEX the same thing?

How many PRIMARY KEYs can be per table on MySQL (some sources say only one, some that it is more, depending on SQL version?)

In phpMyAdmin there is something like "power" of KEY, what it means? And since this (and all KEYs) is listed below "INDEXES" does it mean that KEY is a synonym to INDEX?

What if I made a table without any KEY? Would it have any effect apart from no INDEX?

To me it seems like there are 3 types of indexes/keys PRIMARY KEY = not null + unique value + index, UNIQUE = unique value (but not necessarily indexed?), INDEX = index.

[JGadrow]

Yes, "KEY" is a synonym for an "INDEX."

A table may only have one PRIMARY KEY, but you can create a "compound key."

As for "power" of the key... are you referring to the cardinality?

Creating a table without a KEY is the same as creating a table without an INDEX.

As far as the 3 types of indexes, you are correct except that UNIQUE is still an index. If you use InnoDB, there is also FOREIGN KEY which is used to map an index value to the primary key of another table so that data integrity can be preserved when alterations are made to the source row.

[Chris]

LOL, and I was thinking so hard what it was. Couldn't they have just named it PRIMARY INDEX?

As for "power" of the key... are you referring to the cardinality?

Yes, in English it would be cardinality http://en.wikipedia.org/wiki/Cardinality_%28SQL_statements%29
I assume that PRIMARY KEY of 100 entries table will always have cardinality 100? Or is it calculated some other way?

[Harkins]

Cardinality is a measure of how unique the data is in the column. If you have an int column but the numbers always range from 0-5, you have a low cardinality - an index would not help much here.

It's called a key because it's the way to uniquely identify a particular row. Web apps very often use synthetic keys, like uniquely numbering each row instead of creating a key based a set of columns that will be unique (eg. email address for users, or pet name + owner name for a pet in a pet training game). The key is the minimum you need to join against the table. An index is just a performance optimization.

[aerosuidae]

LOL, and I was thinking so hard what it was. Couldn't they have just named it PRIMARY INDEX?

"PRIMARY KEY" is ANSI SQL, not a MySQL-ism.  A PRIMARY KEY only has to be a unique identifier for a single record, and technically that means does not need to be indexed.  Hence, KEY != INDEX, in theory.  However, in practice, a UNIQUE INDEX is the most efficient way to guarantee PRIMARY KEY uniqueness, so, most of the time KEY == INDEX 

I assume that PRIMARY KEY of 100 entries table will always have cardinality 100? Or is it calculated some other way?

Cardinality is storage engine dependent.  For your MyISAM tables, if your 100 entries are all unique values, the index cardinality should be 100 (but may require an ANALYZE TABLE).