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.