> From: Bennett Haselton > > My databases textbook from college says that specifying an > attribute as an > "index" means that the data will be stored in such a way that > lookups on > that attribute are faster, and specifying an attribute as a > "key" means > that its values have to be unique.
If you are quoting your textbook correctly I believe it is simply wrong (or at least not conveying long accepted ideas). I know it adds nothing to the validity of what I have to say but nearing forty years of active and sometimes not so active software development I have read many texts that lack rigor. Just say "S" "Q" "L" to some and they immediately want to correct you by using a term that actually refers to an old commercial product (IMHO "S" "Q" "L" is more better:). This is at the heart of your observation, common usage or interpretations versus clearly defined terms that are necessary to communicate concepts. The MySQL manual does a good job in presenting information correctly or at least consistent with the prevailing standards. Actually I don't see anything wrong in the manual regarding keys or indexes. Common usage of "key" and "index" often interchange the terms because of the close relationship to the DBMS operations to which they relate. More rigorously, a key is a field (or the value of a field). An index is conceptually a container of keys arranged in such a way that each key in the index refers to a unique record. In practice indexes may or may not increase speed to access a particular record. That's why you profile and tune. Generally, indexing for speed is a correct assumption. An index can be unique or non-unique (contain unique key values or non-unique key values). In the unique case each entry in the index refers to a unique record. In a non-unique index entries with the same key value still refer to unique records, some index entries are not unique in their key value. An important concept is that the record is the unique entity not the key. In fact multiple records without a primary key can contain exactly the same data but they are still unique as to their address in the table and can be indexed (or keyed) with a non-unique index. There are many ways to implement indexes but a list is the easiest to understand. For some data structures you can have a key and not have an index but if you have an index you must have a key. Understanding the internals of the thing is what you need for critical thought. These definitions come very close to my understanding of the terms and you can find similar ones in Donald Knuth's work. As with all things one man's standard is another man's chaos. The following is quoted from webopedia.lycos.com. ------------- KEY In database management systems, a key is a field that you use to sort data. It can also be called a key field , sort key, index, or key word. For example, if you sort records by age, then the age field is a key. Most database management systems allow you to have more than one key so that you can sort records in different ways. One of the keys is designated the primary key, and must hold a unique value for each record. A key field that identifies records in a different table is called a foreign key. INDEX In database design, a list of keys (or keywords), each of which identifies a unique record. Indices make it faster to find specific records and to sort records by the index field -- that is, the field used to identify each record. (My comments.. Note that each entry in an index is always unique but it is unique in that the address of the record and the key form the entry.) SQL Abbreviation of structured query language, and pronounced either see-kwell or as separate letters. SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center in 1974 and 1975. SQL was first introduced as a commercial database system in 1979 by Oracle Corporation. ------------- I hope my comments are helpful and inoffensive. No offense is intended. Its all just for fun. Peace, Norman L. Smith --------------------------------------------------------------------- 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
