Database Indexing – an Introduction

Databases are excellent data storage and retrieval tools, ranging from simplistic tables of information to complex, intricate collections of multiple linked databases. A database is created and named based on the particulars of its application, and stores data in tables consisting of an organized collection of rows and columns. Data may be manually entered into databases, but is more commonly written dynamically into databases by web sites or by connected software applications. Companies often maintain constantly growing databases containing data such as client information, financial figures and performance analyses, for instance, and the ever expanding nature of such databases warrants techniques for streamlining the process of locating specific pieces of information within them. Indexing allows for the rapid retrieval of specified data by queries, and is therefore a powerful tool for databases containing hundreds or even millions of records.

At initial set up, the creator of a database typically defines the primary key, which is a column within which the data in each row is unique. For example, a column containing client last names would not be identified as the primary key, as multiple entries may have the same name. However, a column containing unique client ID numbers could be designated as the primary key. When the primary key is defined, an index is actually created. When data from the column defined as the primary key is requested in the form of a query, the database automatically utilizes this index to quickly locate the requested data. However, the primary key is likely not the only column that will need to be searched for data. For queries that will be used frequently, an index can be added to the appropriate column to speed up the process of data selection. Indexes speed up the process of retrieval while lessening the workload the server must endure to perform the search.

Much like the way the index of a book makes finding a topic easier, database indexes make locating a queried piece of data easier. Instead of flipping through and reading each individual page of a book to locate a topic, you can refer to the book’s index and know where to look right away. Likewise, instead of a query asking each record in a database if it contains the information being sought, it can be directed by an index to the appropriate data.

Effective use of indexes requires establishing a balance between an efficient number of indexes and too many. Since indexes take up space, using too many can counteract the purpose by slowing the database down. Indexes may be unnecessary for smaller databases, where the queries can locate data quickly and easily. But indexes are dynamic and powerful tools for optimizing the performance of large databases.