"An index is an orderly arrangement used to logically access rows in a table." (Coronel, Rob)
The purpose of an index is to speed up access and searching of databases. It does this by using an index key and a pointer. The index key is the reference point and the pointer is where the data is located. An index is basically a table full of index keys and pointers for the data in the database.
Indexes can also be used in ordering of attributes. An index of names could be sorted alphabetically allowing the user to browse or search easily.
A form of an index is a unique index, which only allows one pointer per index key. A common example is of a primary key index, the DBMS automatically creates this when you specify a primary key.
Multiple indexes may be created for each table. Examples would be ordering a Student table by last name, student status, date of birth, or year.
An index may only contain keys for one table, you cannot cross index tables.
Different kinds of pointers
- Absolute- The true physical location of the data on the disk
- Relative- Offset from a known point. Example: In an employee database with an alphabetical index of last names, if you know where the last A name is then you can easily calculate where the first B is and go from there through the whole alphabet.
- Logical- Uses some form of algorithm to calculate the location. Example: In an employee database with an alphabetical index of last names, you could cut the alphabet in half, see if the first letter of the last name is in that set, then continue to cut it in half to narrow down on the exact location of the data.