> Hello all being relatively new on the SQL scene I must say some of the
> concepts are a little new and strange to me. My really big question is what
> is the difference between an index and a key? are the synonamous with each
> other?
A key will identifying a row or several rows. Just like you key to
your house can "identified" by you house lock. But the key wont tell
you were your house is located, this is what the index will do for you.
An index is just a smart way to skip some rows that is not needed to
search while you look for your data. For example if you have a ordered
list of numbers from 1 to hundred, then you know you dont have to look
in the upper part of the list if your number is less than 50.
So the purpose of the index is to locate where you can find a match
for your key, in other words exclude parts which is guaranteed not
to include the key.
Analogue example of index and none indexed search:
None indexed unique search:
Assume you live in a skyscraper. You start at the bottom floor
and test your house key in every single lock until you find
a match, if you don't find a match you progress to the next floor
and repeat the procedure. If you find a match you stops seating
None indexed none unique search:
You start at the bottom floor and test your master key in every
single lock until you find a match. If you find a match, you mark
that door, and continue search the rest of the doors, until you
reached the top floor.
Indexed unique search:
You go to the elevator, selects the floor which match your
key, arriving at this floor you tries out all doors until
you find one match.
Indexed none unique search:
You go to the elevator, with a list of floors you need to
visit. You visit each floor specified in the list and on
arrival at each of these floors you test all doors at that
floor and mark the doors that has a match.
Basically an index is specifying a lesser search space for you,
so you don't have to brute force search an entire table. A brute
force search is normally refereed to as a "table scan". While
you key is the value that actually precesly can identify your
data, it is a slow way to find it since brute force search is
needed to locate yuor data if you only uses the key. The index
has the ability to very fast narrow down your data, but to
the cost of resultion - the index doesn't well on close
distance. Hence the index can't identify your data, just
tell you an "about where" location.
In a way you can put it like this:
The index has good sight at long distance, but bad at short,
while the key has bad sight on long distance but good sight
on short.
//Anders
---------------------------------------------------------------------
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
