Fog Creek Software
Discussion Board




The difference between a KEY and an INDEX in MySQL

Can someone please point towards information on keys and indexes? What exactly do they *do*, specifically within MySQL?

Thanks.

Steve %
Wednesday, June 16, 2004

To properly understand the distinction you need to understand the relational model rather then MySQL itself.

There's some good stuff on WikiPedia: http://en.wikipedia.org/wiki/Relational_model .

The main difference between a Key and an Index is that the Key is part of the logical model, and an Index is part of the implementation.

All tables must have a primary key, which is used to uniquely identify each of the relation's entries.  It may also have foreign keys, which link tables together.

Indexes are used to make searching by keys faster, and to help enforce constraints.  Constraints might be used to ensure that the primary key is unique, or that the foreign key references a valid entry in its target table.

Ged Byrne
Wednesday, June 16, 2004

What Ged says is true for relational databases in general.  MySQL requires every Key also be indexed, that's an implementation detail specific to MySQL to improve performance.

http://mysqld.active-venture.com/MySQL_indexes.html

Tom H
Wednesday, June 16, 2004

Rereading your question, I see your asking what Indexes actually do, so I thought I'd go into a little more detail.

Indexes are used for giving you fast access to a specific piece of information.  They are just like the index at the back of a book.

A book contains lots of words, in no order other than how the author is expressing themselves.  The index contains each word just once, and they are in alphabetical order.

If you want to find every occurance of a word, you just look at the index and you get a list of page numbers.  You then scan each of these pages to find the word.  It doesn't take very long.

Imagine if you wanted to find every occurance of a word without using the index.  You would have to read the entire book.  This would take ages.

Ged Byrne
Wednesday, June 16, 2004

I also believe that MySQL's 'KEY' is somewhat like specifying something as a candidate key -- namely MySQL will enforces uniqueness on that attribute -- whereas specifying an index on a column will not make MySQL check for duplicates.

Captain McFly
Wednesday, June 16, 2004

In MySQL, KEY is equivalent to INDEX. It won't check for duplicates without UNIQUE in front of it.

Egor
Wednesday, June 16, 2004

Boy, they should really change the name or behavior then, because that's a *big* departure from the relational definition of "KEY".

Captain McFly
Wednesday, June 16, 2004

*  Recent Topics

*  Fog Creek Home