Besides the primary key and the foreign key, there
is another common key called an index. The concept
of database indexes is exactly the same as the index
found at the back of a good book. For a book with
hundreds of pages, it will be a nightmare trying to
look for a particular word or phrase in the book.
The reader will instead flip right to the back of
the book and look for the particular word or phrase
in the index section. The index section will tell
the reader which page(s) in the book to zoom in to
look for the word or phrase of interest.
In a database table, the database designer may specify
some columns as index keys. The database engine will
index all the values in these index keys so that when
users perform a search on the table using the column
as part of the search criteria, the database engine
can quickly and efficiently find the required record(s).
A good rule of thumb is that any column on which a
search is to be performed needs to be indexed. If
a search is performed on a column that is not an index,
the database engine will need to parse through all
the records in the table in order to find all records
matching the search criteria. This will be a major
problem and a huge drain on computing resources if
the table contains a huge number of records.
The downside to creating index keys is that the database
engine typically needs to create an index file to
store these indexes. The database engine needs to
be aware of any changes to any index key values in
the database table in order to update this index file.
Index information is affected for every table INSERT,
UPDATE or DELETE operation. This directly increases
the database server's storage and computing resource
requirements.
