I've not checked your syntax but it looks like you've got the idea. One caveat. Unless you have a UNIQUE index, you can have multiple people named John Smith. If you specify UNIQUE in your index clause then you can't.
=C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -----Original Message----- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 1:17 PM To: Svensson, B.A.T. (HKG); [EMAIL PROTECTED] Subject: Re: Simple Question relating to Indexing I'd like to first thank Carl and Anders for your quick and very informative answers. I really appreciate it. To get this staightened out, for a table specified by: CREATE TABLE Player ( PlayerID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, TeamID INT NOT NULL, TrophyFlag TINYINT, RecordFlag TINYINT, LetterID TINYINT NOT NULL, NameFirst char(15) NOT NULL, NameLast char(20) NOT NULL) My primary key is obviously: playerid. letterID, trophyflag, recordflag, and teamid can be foreign keys. They are not configured so right now. An index could be created based upon NameFirst and NameLast. Which would be ok so long as there are not two people with the name John Smith (or whatever) ----- Original Message ----- From: "Svensson, B.A.T. (HKG)" <[EMAIL PROTECTED]> To: "Shaun Bramley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 1:11 PM Subject: RE: Simple Question relating to Indexing > > 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 --------------------------------------------------------------------- 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
