What is a KEY and INDEX

[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.