What is a KEY and INDEX

[Guest190829]

Can someone tell me what exactly a KEY and an INDEX is, relating to mySQL tables. I tried looking at mysql.com, but I'm just getting confused. I understand what a Primary Key is. It's the unique field in a table. But I'm not really sure on the subject...and why are they so important?

Any help will be greatly appreciated! :)

[Paul M]

In index is basically an ordered lookup for table records based on the defined Key (which is a list of which fields from the table the index should use).

So if, for instance, you have a simple user table with name, password & joindate fields - and you want to select the users alphabetically - without an index, sql has to sort all the records before outputting them - which is slow.

Therefore what you do is add an INDEX based on the name field (which is therefore the KEY). This index basically contains the key (in sorted order) and a pointer to each record. When a new record is added/deleted/modified the index is updated at the same time.

So to select the records in order, all sql has to do is pull the index records sequentially and select the records from the main table that each index record points to - eliminating the need to sort the records - therefore it's much faster.

My explanation is probably a bit poor but hopefully you get the idea.

[Guest190829]

I get the idea now. Thanks alot Paul, you really help me out. :)