KEY is used to build other indexes. It's not short for Primary Key. There
are PRIMARY KEY indexes, candidate key indexes (these COULD be a primary key
but for one reason or another , are not) and just regular indexes.
All tables should have a primary key. (I'm partial to adding an
auto_increment field to almost every table of the name <tableName>ID and
making this my primary key) Some tables will also have a candidate key. This
is especially true if you manufacture a PK like I do. Then there may be a
piece of data that is unique to each record and never null that would
normally serve as a primary key. (The reason I manufacture PKs is because if
a piece of data means something then it is always possible that it will
change.This means that you would have to trace down all your FK
relationships and change the data in those tables as well.SSN, phone number,
email address are all examples of candidate keys but also smart keys. I
never use them as PKs. )
Regular keys are just indexes, not necessarily unique, not necessarily on
fields that don't accept nulls. Their primary function is to speed up data
retrieval. Use them sparingly as they can have a negative impact on
inserting and updating records.
HTH,
Cal
*
* Cal Evans
* Senior Internet Dreamer
* http://www.calevans.com
*
----- Original Message -----
From: "Marco Bleeker" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 22, 2001 9:36 AM
Subject: KEY and index
> Hello, I am learning MySQL and have a question. I know about the
importance
> of the PRIMARY KEY statement, but I also see a KEY statement being used
> when creating a table. Is this just short for PRIMARY KEY, or what's the
> difference?
>
> Second question, when I have a large table, but with only 2 small cells
per
> row (2 columns), is it useful to create a PRIMARY KEY, KEY, or INDEX for
> speed (there is no set relation to another table).
>
> Third question, when exactly does MySQL use indexes. Is it used when the
> indexed column is part of a (SELECT *) WHERE statement, together with a
> non-indexed column? I just want to check if matching a row is present or
> not, not actually retrieve information ("WHERE ip='$ip' and
date>curdate()")
>
> Thanks, Marco
> |
> | Marco Bleeker, Amsterdam
> | [EMAIL PROTECTED]
> | http://www.euronet.nl/users/mbleeker/
> |
> | Attachments only after prior notice please.
> | Don't put me on any kind of mailing list.
> |
> | I am now receiving the Snowhite virus 4x a day
> | - some of you must be infected, please check !
> | (No, you did not get it from me, I use Eudora)
> | __@
> | _`\<,_
> |__(*)/ (*)________________Ah, op DIE fiets !
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
