by Greg Specht

Created on: December 08, 2009

The purpose of a database is to store huge amounts of information, and to retrieve that information for use or display quickly.  The mystery is in how a database system stores and retrieves this information as efficiently as possible.  That is where an index system for database records comes in. 

Indexing a database table allows the database to retrieve a specific piece of data in a fraction of the time it would otherwise take to get the same information without the index.  To understand how this works, it is necessary, first, to look at how a database looks up information without an index.

A database table of names and addresses, such as a public phone book, with over a million entries will help to understand database indexing.  A good example would be a list of names and addresses of all people in a given zip code.  Without an index, the database must look at each record in turn, to see if it matches the query and decide whether to include that record in the results.

With an index on the same table, using the zip code field as the index, a list of names and addresses from a specific zip code will be returned in a fraction of the time.  This is possible because the index simply contains a list of all the records that contain a specific value in the indexed field.  Therefore, when retrieving a list of names and addresses in a specific zip code, the index already has that list ready to go.

 If the returned list is to be further refined, perhaps by name, such as all people named ‘Jones’ in zip code ‘12345’, the index provides a list of all records  with that zip code, and that list can be checked for people names ‘Jones’.  This is much faster than looking at over a million records, one at a time, to see if the zip code and name match the criteria. 

 The performance gained by indexing a database table does come at a cost, however.  The index resides in a separate computer file from the database table, meaning that indexed data uses more disk space than non-indexed data.  In addition, when updating indexed data, both the table data and the index data must be updated, resulting in much slower performance in updating indexed data. 

 The main thing to remember is that carefully considering which tables and fields in a database to index, can result in substantial performance benefits.

Learn more about this author, Greg Specht.
Click here to send this author comments or questions.