As your database grows in size, the data stored can be of tremendous value to your organization – if you can retrieve the data in a meaningful way to reap the benefits. This is the key to a successful database, being able to write to the database at the required rate and volume, and then being able to read the data stored in a high-performance, useful manner.
As a general rule, the place to start when you encounter database read performance issues is to optimize your single monolithic DBMS instance. This should be done prior to scaling your database, and often provides a very quick return on improving results. In other words, improving the performance of a monolithic DBMS instance is normally very effective when you find that database reads are slow, and database writes are performing acceptably.
Note: When your monolithic DBMS can no longer accommodate your database write load, this is the time to consider scaling your database for Big Data (the subject of many future articles…).
The purpose of Database Indexing is to improve database read performance. Understanding what in index is, and how it works is the key to implementing a successful Database Indexing strategy.
What is an Index?
In a prior article, I discussed the Enemies of Database Performance (see [link here]). The #1 Enemy is the Table Scan, a sequential read of a table from top to bottom in order to find the requested rows or objects. A Table Scan can almost always be avoided by adding an Index.
Think of a Database Index as a separate, optimized copy of a portion of a data structure or table, stored in such a way as to optimize reading that data for a specific purpose. It’s easiest to compare a Database Index to the card catalogue in your local library – you look up the book you want to find in the card catalogue, and it points you to the location of the book on the appropriate shelf. The card catalogue is sorted in such a way so that finding the book you want is easy (such as by title or author). Imagine what would happen if you could not find the book in the card catalogue, your only alternative would be to search all the shelves in the library – a very time consuming task. In fact, a Table Scan is exactly analogous to searching the entire library, and gives you an idea just why it causes such notable performance degradation.
THIS IS A PREVIEW. DOWNLOAD ISSUE 10 TO READ THE FULL ARTICLE.