Database index

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.


See also: Primary keys, Foreign keys and Composite keys