A database index is a data structure that improves the speed of operations on a database table. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records. Indexing is incredibly important when working with large tables, however, occasionally smaller tables should be indexed, if they are expected to grow. Small tables that will remain small, however, should not be indexed (for example, if your book is 1 page, does it make sense to turn to the index?)
Many developers write their code and test it on a table with 10, or 100 rows, and are satisfied when their code performs adequately. However, as the table grows to 10,000, or 1,000,000 rows, the code slows to a snail’s pace, and the client might as well go out to lunch waiting for the code to execute.
When a query searches a database for a matching record, there are two ways in which the search can be performed.
- The first, and the slowest way is a table scan. In a table scan, the query searches every record in the table looking for a match.
- The second, and the faster way is an index scan. In an index scan, the query searches the index to find the record. In non-database terms, a table scan would be the equivalent to reading every page in a book looking for a word, while an index scan would be the equivalent of flipping to the back of the book, finding the word, flipping to the specified page, and then reading the words on the page to find the word.
It is important to remember that indexes need to be rebuilt occasionally, as data is added to the table. Additionally, while indexes increase data access performance, it slows the modification of data. Because of this, most DBMSes have an option to temporarily disable an index to facilitate mass data modification, and then allow it to be re-enabled and rebuilt later.